Support for multiple foreign keys in one table.

[JagdishKolhe]

• SDV version: 0.4.0
• Python version: 3.6.9
• Operating System: CentOS

Description

This is new feature. many of the real databases has such scenarios.

[csala]

Hi @JagdishKolhe would you mind providing a few more details about what you mean in this case?

Do you mean supporting something like having multiple relationships existing between two tables?

For example, one such scenario might be:

• You have an employees table that has an employee_id as the primary key.
• You have a tasks table with, among other things:
  • assignee_id: The employee to whom the task is assigned - defines a first Foreign Key to employees
  • supervisor_id: The employee who will supervise the task - defines a second Foreign Key to employees

Is this what you mean?

[kvrameshreddy]

Hi @csala,
Does SDV work for the above scenario ?

[JagdishKolhe]

@csala, Thanks for attention .

Above can be one scenario, I have not yet thought of much on that.
But we have similar scenario as follows.

user table with primary key user_id
product table with primary key product_id
order table with primary key order_id and TWO foreign keys (user.user_id and product.product_id)

[csala]

Hi @JagdishKolhe what you are describing seems to be a multi-parent scenario, which was added in #162

You can test it using the got_families demo, which has this structure:

>>> from sdv import SDV
>>> from sdv.demo import load_demo
>>> 
>>> metadata, tables = load_demo('got_families', metadata=True)
>>> 
>>> sdv = SDV()
>>> sdv.fit(metadata, tables)
>>> sdv.sample()
{'characters':    character_id      name  age
0             0      Arya   14
1             1      Arya   22
2             2      Robb   25
3             3      Robb   21
4             4  Daenerys   16
5             5      Bran   17
6             6     Sansa   21, 'character_families':    character_id  family_id    type  generation
0             0          4    both           4
1             0          5  mother           7
2             1          5  mother           7
3             2          5  mother           7
4             0          6    both           6
5             1          6    both           6
6             0          7    both          12
7             1          7    both          11, 'families':    family_id       name
0          4  Lannister
1          5      Tully
2          6  Lannister
3          7  Lannister}

You can also see an example of how to define a schema like this in issue #193

[Wim65]

Hi , would it preserve column to column correlation between columns in the parents tables ?

(This is Wim Blommaert )

[abhisheknagar1983]

@csala : It clearly evident in your example (got_families) that character_id = 0 is being generated with all the combinations of family_id (4,5,6,7) and same is with the case of character_id = 1 which is also being generated with family_id (5,6,7).

Now the question arias how the samples are being generated?

1. Based on cardinality ? (1:1 or 1:N)
2. In your example there is no rows generated for character_id ;= (3,4,5,6), so it seems that system is generating data for only few of the parent table (not for all).

Note: We try the multi parent scenario based on our data model and our results alos looks similar.

Kindly let us know how sampling works in multiparent scenario and does the systems considers the correlation while between columns?

[csala]

Multi-foreign-key scenarios are supported after #298 so this can be closed.