Database design question

Reply

•
•
Join Date: Sep 2008
Posts: 1
Reputation: RockabillyJim is an unknown quantity at this point 
Solved Threads: 0
RockabillyJim RockabillyJim is offline Offline
Newbie Poster

Database design question

 
0
  #1
Sep 16th, 2008
I have two tables with foreign keys to the same parent table and a junction table between them to map a many to many relationship. I want to know how best to enforce data integrity in the junction table so that both parents' parent is the same.

Example:
sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE Parent
  2. (
  3. ParentCode CHAR(3) NOT NULL,
  4. Name VARCHAR(50) NOT NULL,
  5. PRIMARY KEY (ParentCode)
  6. );
  7.  
  8. CREATE TABLE Child1
  9. (
  10. Child1Code CHAR(3) NOT NULL,
  11. Name VARCHAR(50) NOT NULL,
  12. ParentCode CHAR(3) REFERENCES Parent(ParentCode)
  13. PRIMARY KEY (Child1Code)
  14. );
  15.  
  16. CREATE TABLE Child2
  17. (
  18. Child2Code CHAR(3) NOT NULL,
  19. Name VARCHAR(50) NOT NULL,
  20. ParentCode CHAR(3) REFERENCES Parent(ParentCode)
  21. PRIMARY KEY (Child1Code)
  22. );
  23.  
  24. CREATE TABLE ChildJunction
  25. (
  26. Child1Code CHAR(3) NOT NULL,
  27. Child2Code CHAR(3) NOT NULL,
  28. Attribute INT NOT NULL,
  29. PRIMARY KEY (Child1Code, Child2Code)
  30. );
How do I ensure that the Child1 and Child2 in ChildJunction have the same Parent?

The best I have so far is that ChildJunction includes a ParentCode column, I create a UNIQUE constraint on Child1Code + ParentCode in Child1 and a UNIQUE constraint on Child2Code + ParentCode in Child2 and the FOREIGN KEYS from ChildJunction are ChildJunction.Child1Code = Child1.Child1Code AND ChildJunction.ParentCode = Child1.ParentCode and ChildJunction.Child2Code = Child2.Child2Code AND ChildJunction.ParentCode = Child2.ParenCode.

But that leaves me with redundant data in ChildJunction (i.e. ParentCode).

Am I missing something? This must be a common problem? have I designed the Child tables wrong? Or the Parent table?

Any help gratefully received...

Thanks,
Simon
Last edited by cscgal; Sep 16th, 2008 at 9:48 pm. Reason: Added code tags
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2008
Posts: 1,162
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 138
dickersonka dickersonka is offline Offline
Veteran Poster

Re: Database design question

 
0
  #2
Sep 17th, 2008
Normally what you would do is have a single child table, with an additional column for child_type_id

CREATE TABLE ChildType(
CHILD_TYPE_ID int NOT NULL,
CHILD_TYPE_NAME VARCHAR(20),
PRIMARY_KEY(CHILD_TYPE_ID)
);

CREATE TABLE Children(
ChildCode char(3) NOT NULL,
Name varchar(50) NOT NULL,
CHILD_TYPE_ID int NOT NULL REFERENCES ChildType(CHILD_TYPE_ID),
ParentCode char(3) REFERENCES Parent(ParentCode)
PRIMARY KEY (ChildCode)
);

populate ChildType with your 2 entries of what child 1 and 2 are, lets say with CHILD_TYPE_ID's of 1 and 2

this way fk are in place, and if you need to query what would be in the old 'Child2' you can use 

-- child 2 query
select * from Children where CHILD_TYPE_ID = 2;

-- child 1 query
select * from Childrean where CHILD_TYPE-ID = 1;
Last edited by dickersonka; Sep 17th, 2008 at 12:02 pm.
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the Database Design Forum


Views: 695 | Replies: 1
Thread Tools Search this Thread



Tag cloud for Database Design
assignment avatar backup bigbrother business calendar daniweb data database databasedesign design erd europe events hacker hp ibm medicine mobilephone mysql news recovery security server sql survey uk update
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC