many-to-many tables not supported by dm_examine_constraints

[smgogarten]

I have a data model that includes a many-to-many table, which I was able to validate with earlier versions of the dm package. Now, using dm_examine_constraints on the same model produces a validation error. It appears that the function is defining a key type "unique key", even when I have not set a primary key in the model. The documentation says that the output of dm_examine_constraints includes a vector kind with values "PK" or "FK", but it actually creates additional keys with kind "UK" not set by the user.

Here is a reproducible example:

sample <- tibble(
    sample_id = c("sample1", "sample2", "sample3"),
    property = c("A", "B", "C")
)
sample_set <- tibble(
    sample_set_id = c("set1", "set1", "set2", "set2"),
    sample_id = c("sample1", "sample2", "sample2", "sample3")
)
dataset <- tibble(
    dataset_id = c("dataset1", "dataset2"),
    sample_set_id = c("set1", "set2")
)

model <- as_dm(list(sample = sample,
          sample_set = sample_set,
          dataset = dataset))
model <- dm_add_fk(model,
                   table = "sample_set",
                   columns = "sample_id",
                   ref_table = "sample",
                   ref_columns = "sample_id")
model <- dm_add_fk(model,
                   table = "dataset",
                   columns = "sample_set_id",
                   ref_table = "sample_set",
                   ref_columns = "sample_set_id")

(chk <- dm_examine_constraints(model))
! Unsatisfied constraints:
• Table `sample_set`: unique key `sample_set_id`: has duplicate values: set1 (2), set2 (2)

str(chk)
dm_examine_constraints [4 × 6] (S3: dm_examine_constraints/tbl_df/tbl/data.frame)
 $ table    : chr [1:4] "sample_set" "sample" "dataset" "sample_set"
 $ kind     : chr [1:4] "UK" "UK" "FK" "FK"
 $ columns  : keys [1:4] 
  ..$ : chr "sample_set_id"
  ..$ : chr "sample_id"
  ..$ : chr "sample_set_id"
  ..$ : chr "sample_id"
  ..@ ptype: chr(0) 
 $ ref_table: chr [1:4] NA NA "sample_set" "sample"
 $ is_key   : logi [1:4] FALSE TRUE TRUE TRUE
 $ problem  : chr [1:4] "has duplicate values: set1 (2), set2 (2)" "" "" ""

 sessionInfo()
R version 4.1.2 (2021-11-01)
Platform: aarch64-apple-darwin20 (64-bit)
Running under: macOS Big Sur 11.6

Matrix products: default
BLAS:   /Library/Frameworks/R.framework/Versions/4.1-arm64/Resources/lib/libRblas.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/4.1-arm64/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] dm_1.0.0

loaded via a namespace (and not attached):
 [1] igraph_1.2.11     magrittr_2.0.2    hms_1.1.1         progress_1.2.2   
 [5] tidyselect_1.1.1  R6_2.5.1          rlang_1.0.4       fastmap_1.1.0    
 [9] fansi_1.0.2       dplyr_1.0.9       tools_4.1.2       utf8_1.2.2       
[13] cli_3.2.0         DBI_1.1.2         ellipsis_0.3.2    assertthat_0.2.1 
[17] tibble_3.1.6      lifecycle_1.0.1   crayon_1.5.0      purrr_0.3.4      
[21] tidyr_1.2.0       vctrs_0.4.1       memoise_2.0.1     glue_1.6.1       
[25] cachem_1.0.6      compiler_4.1.2    pillar_1.7.0      prettyunits_1.1.1
[29] generics_0.1.2    backports_1.4.1   pkgconfig_2.0.3  

[krlmlr]

Thanks. A "many-to-many" relationship is typically modeled with an intermediate table that has two foreign keys.

Can you please describe the relationships in your data? Does one sample_set really "contain" multiple dataset-s, or is it more the other way round? Could your example also be rewired like this:

library(conflicted)
library(dm)

sample <- tibble(
  sample_id = c("sample1", "sample2", "sample3"),
  property = c("A", "B", "C")
)
sample_set <- tibble(
  sample_set_id = c("set1", "set1", "set2", "set2"),
  sample_id = c("sample1", "sample2", "sample2", "sample3")
)
dataset <- tibble(
  dataset_id = c("dataset1", "dataset2"),
  sample_set_id = c("set1", "set2")
)

model <- as_dm(list(
  sample = sample,
  sample_set = sample_set,
  dataset = dataset
))
model <- dm_add_fk(model,
  table = "sample_set",
  columns = "sample_id",
  ref_table = "sample",
  ref_columns = "sample_id"
)
model <- dm_add_fk(model,
  table = "sample_set",
  columns = "sample_set_id",
  ref_table = "dataset",
  ref_columns = "sample_set_id"
)

model %>% dm_draw()

model %>% dm_examine_constraints()
#> ℹ All constraints satisfied.

^{Created on 2022-08-06 by the reprex package (v2.0.1)}

[smgogarten]

That would work if there were only one dataset per sample_set, but it's possible to have multiple datasets for the same sample_set:

sample <- tibble(
    sample_id = c("sample1", "sample2", "sample3"),
    property = c("A", "B", "C")
)
sample_set <- tibble(
    sample_set_id = c("set1", "set1", "set2", "set2"),
    sample_id = c("sample1", "sample2", "sample2", "sample3")
)
dataset <- tibble(
    dataset_id = c("dataset1", "dataset2", "dataset3"),
    sample_set_id = c("set1", "set2", "set1")
)

x <- list(sample = sample,
          sample_set = sample_set,
          dataset = dataset)

model <- as_dm(x)
model <- dm_add_fk(model,
                   table = "sample_set",
                   columns = "sample_id",
                   ref_table = "sample",
                   ref_columns = "sample_id")

model <- dm_add_fk(model,
                   table = "sample_set",
                   columns = "sample_set_id",
                   ref_table = "dataset",
                   ref_columns = "sample_set_id")

model %>% dm_examine_constraints()
! Unsatisfied constraints:
• Table `dataset`: unique key `sample_set_id`: has duplicate values: set1 (2)

[krlmlr]

Can you have multiple sample sets per dataset, and multiple datasets per sample set? What are the semantics of sample_set_id ? What should happen e.g. with two sample sets and three datasets that share the same sample_set_id -- are all sample sets connected with all datasets and form the same "group", or are only some of the sample sets connected with some of the datasets?

[krlmlr]

If each dataset has one sample set, how is this a many-to-many relation? Is your many-to-many relation between sample_set and sample, i.e. one sample set can have multiple samples, and one sample can be associated with multiple sample sets?

If this was the case, then dataset becomes a child table of sample_set (as in your original example), and we need a link table that is a child to both sample_set and sample -- neither would be directly related to each other.

[smgogarten]

Right, the many-to-many relation is between sample_set and sample.

[krlmlr]

Then the following might be what we are looking for? We need the new set table for the uniqueness constraint, and you might have attributes per set that you then don't need to repeat in the sample_set table.

library(conflicted)
library(dm)

sample <- tibble(
  sample_id = c("sample1", "sample2", "sample3"),
  property = c("A", "B", "C")
)
sample_set <- tibble(
  set_id = c("set1", "set1", "set2", "set2"),
  sample_id = c("sample1", "sample2", "sample2", "sample3")
)
set <- tibble(
  set_id = c("set_1", "set_2")
)
dataset <- tibble(
  dataset_id = c("dataset1", "dataset2", "dataset3"),
  set_id = c("set1", "set2", "set1")
)

model <-
  dm(sample, sample_set, set, dataset) %>%
  dm_add_pk(sample, sample_id) %>%
  dm_add_pk(set, set_id) %>%
  dm_add_pk(dataset, dataset_id) %>%
  dm_add_fk(dataset, set_id, set) %>%
  dm_add_fk(sample_set, set_id, set) %>%
  dm_add_fk(sample_set, sample_id, sample)

model %>%
  dm_draw()

^{Created on 2022-08-09 by the reprex package (v2.0.1)}

[krlmlr]

I don't yet understand how "dataset1" and "dataset3" are different in your application.

[smgogarten]

Getting back to this after a vacation... dataset1 and dataset3 would differ in additional columns in the dataset table that I didn't include in my simple example. I think the real problem is that I am trying to use dm to model something that is not strictly a relational database (data tables on the AnVIL platform), so I will need to come up with a workaround for the check I want to do. Thanks for all your help!

[krlmlr]

In your data I now see a third dataset that is related to the first sample set. Is the third dataset also related to the second sample?

[krlmlr]

To accurately model this, we will need a fourth and perhaps a fifth table. The only remaining question I have is where to put it and how to wire it.

This is not really a restriction of the dm package, most databases will enforce the same.