Load data to many - many xref table

Question

Load data to many - many xref table

Joe Contreras-290946

SSCommitted

Points: 1903
More actions
December 7, 2008 at 9:44 am

#203794

Hi,
I was given a design to load data into a many-to-many xreference table. The design is I have a relationship where I can have multiple states associated with a single rate and I can have multiple rates for a single state.
I basically have two one-many relationships, my question is how can I accomplish the load to this table?
I have 2500 records in the state table (which is a cross-join) and I have six records for the rates table. How many records would be loaded into this table? Would it be 2500 * 6?
My reference table would have
XREFID
STATEID
RATEID
How does one go about in loading this data?
Thanks,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Joe Contreras (12/7/2008)
...I can have multiple states associated with a single rate and I can have multiple rates for a single state.
...
I have 2500 records in the state table (which is a cross-join) and I have six records for the rates table. How many records would be loaded into this table? Would it be 2500 * 6?

This sounds like a standard intersection table to solve a many - many relationship in a relational database. I'd say at MAXIMUM you will have 2500 * 6 records in the table, because it probably doesn't make sense for one state to have the same rate listed multiple times. However, I'd also guess that not every state has every rate, and which state has which rates would be determined by some sort of business rules.

What form is the data in currently? I'm assuming whatever business group that determines which states get what rates must be trying to track this information already in some way, and that your database will just be a more efficient way of storing that information. Sounds like you'll need to talk more with the business people.

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 2

Hi,

They track this manually. I was given a matrix layout, which I create a view breaking down the states into Zones (via business rules). Since there is a to and from zone I had to breakdown the states into two categories. Not knowing the business rules that well I had to guess at some of this stuff. Trying to document this as best since this is my last week here.

Thanks,