many-to-many relationship - Missing Combinations...

jean-rator

Hi everybody,


Here's the following context :

- I'm generating data for 3 tables : Address, Company and CompanyAddress ;
- A common use case : CompanyAddress is a many-to-many relationship table composed of 2 fields (FK) referencing Company (IdCompany field) and Address (IdAddress field). those 2 fields composing the PK for the table ;
- concerning CompanyAddress table, if I choose :
. IdCompany : "All key values unique" and IdAddress : "repeat" => I obtain only one row for each IdCompany (there is no repeated value for an identical IdCompany...). but all the values of IdCompany from Company table are convered.
number of rows is the same for the 3 tables
. IdCompany : "repeat" and IdAddress : "All key values unique" => I obtain several rows for each IdCompany, but some IdCompany (Company table) doesn't have any row in the many-to-many table.
number of rows is the same for the 3 tables


Question : Is it posible to obtain 1 or n rows for each IdCompany AND having at least a row for each IdCompany (Company table).

Is it by design or am I missing something ?
Thanks in advance for replying.

Regards.

JL.

James B

Thanks for your post.
I think although a common scenario, this kind of thing actually is hard to work with Data Generator.

The generators only really know about the column in question - so when you said "unique" for IDCompany, it means unique in that column. It doesn't know you mean unique combinations across both columns.

I'm not aware of an easy way around this asides from temporarily dropping any uniqueness clause across the two (relating to the PK), generating the data, and then putting together a SQL query to remove duplicates when accounting for both columns.

It's something I'm hoping we'll be able to improve (along with other changes to the FK generators) but it's not been looked as at yet.

jean-rator

Hi,


I found a way to bypass this limit/behaviour.
due to the fact that I'm using a trial version, there is a 1000 rows limit.

When modifying the number of rows (150) for the "main" tables (Address and Company, containing the PK) and setting 1000 rows for the many-to-many table, I obtain combinations for each IdCompany.
A fixed number for each, corresponding to the ratio : 1000/150 => 6

Regards.

James B

Glad to hear you've made progress - sounds like there aren't too many issues with uniqueness in the tables that often causes problems for other users.