1

Given a parent table of regions consisting of north and south, and a child table of states which are associated with the region, assume that California is both a member of region north and region south. What would be the best way to represent this relationship?

My initial idea is a 3rd table to represent the cross reference. For example:

Table_Region
ID     Value
1      North
2      South

Table_State
ID     Value
1      California

Table_Xref
Region_ID    State_ID
1            1
2            1

It seems like a waste to create the 3rd table just to represent the cross reference but it allows the state table to contain just the unique values for each state. If I added region ID to the state table I would have to repeat the values in the state table for California since it falls into both regions.

aneroid
  • 12,983
  • 3
  • 36
  • 66
pretzelb
  • 1,131
  • 2
  • 16
  • 38
  • 2
    What you're proposing is the usual way of dealing with a many-to-many relationship. Though in this particular case I'd be wondering at the usefulness of the "north/south" attribute. – Joe Apr 26 '14 at 01:46
  • I agree with Joe, that is the normal way. You could also create a Table Region Id 3 Both – Kevin Apr 26 '14 at 01:47

1 Answers1

0

As said in the comments, that is the normal way to design the many-to-many relationship.

If you will have only 2 (or 4) regions, you could do avoid the Region table and store region as an enumerated type in Table_Xref without needing a separate table for Regions. Or just store it as an int where your app/queries account for the region code.

Community
  • 1
  • 1
aneroid
  • 12,983
  • 3
  • 36
  • 66
  • Thanks for the reply. It had been a while since I saw a hierarchy where there wasn't a simple 1-1 parent child relationship and creating a separate xref table seemed odd at first. – pretzelb Apr 29 '14 at 19:26