Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to know what is the best solution for making a parent table with multiple children?

I have to make multiple children table. One for each profile.

Is there a better idea than having 3 foreign keys in the parent table?

share|improve this question
4  
Can you give more specifics? What kind of profiles are we talking about, and how will their columns be modeled differently? Will one row in the parent table have exactly one profile in one of the three tables, or potentially more than one? Also if this is more than just theoretical and you want any platform-specific code you should specify which RDBMS you are using. –  Aaron Bertrand Feb 7 '12 at 22:33
    
Only one the the childs will be linked to the parent. I'm planning to use a lightweight sql DB like noSQL, maybe MySQL but not Access. –  Naster Feb 8 '12 at 21:58
add comment

2 Answers

You can do this (assuming I'm understanding you correctly) via having foreign keys in your child tables referring to the parent table.

CREATE TABLE Parent (
        ParentID INTEGER PRIMARY KEY)

CREATE TABLE ChildOne (
        ChildOneID INTEGER PRIMARY KEY,
        ParentID INTEGER,
        FOREIGN KEY (ParentID) REFERENCES Parent(ParentID) )

CREATE TABLE ChildTwo (
        ChildTwoID INTEGER PRIMARY KEY,
        ParentID INTEGER,
        FOREIGN KEY (ParentID) REFERENCES Parent(ParentID) )
share|improve this answer
    
This way, it's not reversing parent and child? IS the definition of a parent the one who is referenced by the foreign key or the starting table of a query? Now it's SELECT * FROM ChildOne INNER JOIN Parent on... –  Naster Feb 8 '12 at 21:44
add comment

If you want each row in parent table to have details only in one table, you can slightly modify Simon Righarts solution:

CREATE TABLE Parent (
    ParentID INTEGER NOT NULL ,
    subtype CHAR(1) NOT NULL,
    CONSTRAINT PK_PARENT(ParentID,subtype),
    CONSTRAINT CHK_PARENT_SUBTYPE CHECK (subtype IN 'A','B'));


CREATE TABLE ChildOne (
    ChildOneID INTEGER PRIMARY KEY,
    ParentID INTEGER NOT NULL,
    subtype CHAR(1) NOT NULL,
    CONSTRAINT FK_CHILD_ONE FOREIGN KEY (ParentID,subtype) REFERENCES Parent(ParentID,subtype),
    CONSTRAINT CHK_CHIL_DONE_SUBTYPE CHECK (subtype ='A'));

CREATE TABLE ChildTwo (
    ChildOneID INTEGER PRIMARY KEY,
    ParentID INTEGER NOT NULL,
    subtype CHAR(1) NOT NULL,
    CONSTRAINT FK_CHILD_TWO FOREIGN KEY (ParentID,subtype) REFERENCES Parent(ParentID,subtype),
    CONSTRAINT CHK_CHILD_TWO_SUBTYPE CHECK (subtype ='B'));

This way ChildOne will only contain details for A master records, and ChildTwo for B master records.

share|improve this answer
    
What about a parent with a subtype B having a child A? –  Naster Feb 8 '12 at 22:03
    
The design above won't let B parent have A child. In some cases it's useful –  a1ex07 Feb 8 '12 at 22:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.