January 7, 2009 at 2:54 pm
Hi All,
I have an association table with 3 columns (primarykeyId, foreign key to Table1, foreign key to Table2). I am thinking to have a child table pointing to this association table. The association table will have additions and deletions for new associations. Now the new child table will also be affected. Please note this new child table will have parent-child relationship with itself.
Is it a good design or could there be a better design possible?
Thank you,
Hussain
January 8, 2009 at 5:42 am
From what you've laid out, it sounds OK. Usually, when dealing with a many-to-many table like this, I let the foreign keys also define the primary key. Otherwise you need to maintain a unique constraint as well as the primary key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 6:07 am
Grant, thanks for advising on the design, I shall go ahead with it.
Thank you
Hussain
January 8, 2009 at 6:10 am
Hussain (1/7/2009)
Please note this new child table will have parent-child relationship with itself.
Ummmm... that's ok so long as you understand the future ramifications and the code required of having such a hieracrchical table in place.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply