Multiple child tables

Question

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?

Answer 1

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) )

Answer 2

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.