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

The simplest way to populate your database with test data.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Michelle Taylor, Lionel, Emma A, sherr

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

Postby jean-rator » Thu Feb 02, 2012 11:17 am

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.
jean-rator
 
Posts: 13
Joined: Thu Feb 02, 2012 11:06 am

Postby james.billings » Fri Feb 03, 2012 6:08 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby jean-rator » Mon Feb 06, 2012 10:03 am

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.
jean-rator
 
Posts: 13
Joined: Thu Feb 02, 2012 11:06 am

Postby james.billings » Fri Feb 10, 2012 12:09 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Data Generator 2

Who is online

Users browsing this forum: No registered users and 0 guests