Red Gate forums :: View topic - many-to-many relationship - Missing Combinations...
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Generator 2
SQL Data Generator 2 forum

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

Search in SQL Data Generator 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jean-rator



Joined: 02 Feb 2012
Posts: 13

PostPosted: Thu Feb 02, 2012 11:17 am    Post subject: many-to-many relationship - Missing Combinations... Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1113
Location: My desk.

PostPosted: Fri Feb 03, 2012 6:08 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
jean-rator



Joined: 02 Feb 2012
Posts: 13

PostPosted: Mon Feb 06, 2012 10:03 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1113
Location: My desk.

PostPosted: Fri Feb 10, 2012 12:09 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group