December 5, 2008 at 12:12 pm
I have various "primary" tables that share an address table. At this time an address can only point to one record in the primary table, but each primary table can have multiple addresses, so it is not a true many-to-many relationship. I have tossed around a couple of ways to handle the data, and I was wondering if anyone has any thoughts on which is the better design?
---------------------------------------------------------------------------------
Address Table
* Address_Key bigint identity
* ParentTableType
* Parent_Key bigint
* Address data
Parent Table1
* Parent1_Key bigint identity
* Parent data
Parent Table2
* Parent2_Key bigint identity
* Parent data
-------------------------------------------------------------------------------------
OR
---------------------------------------------------------------------------------
Address Table
* Address_Key bigint identity
* Address data
Parent Table1
* Parent1_Key bigint identity
* Parent data
Parent1-Address Table
* Parent1_Key
* Address_Key
Parent Table2
* Parent2_Key bigint identity
* Parent data
Parent2-Address Table
* Parent2_Key
* Address_Key
-------------------------------------------------------------------------------------
Thanks,
Jeffrey Bradley
December 6, 2008 at 4:55 am
The second solution is better. The first is going to set up weird relationships. It'll be difficult to query against and hard to maintain. The second solution will make for very straight forward queries and it'll be easy to index.
Now, can an address be used by more than one parent? If so, no worries. If not, you'll need to address that in the two interim tables you're using between parent and address. That's a case for triggers.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply