Optional Parent-Child relationship

database-designreferential-integrity

I have a problem modeling a relationship whereby the child may or may not be related to a parent. For example. You have a Band database with a Tour(parent) table, and a Gig/Concert(child) table. Now, a Tour is a compilation of 1 or more Gigs, but a Gig may also be held off-tour. How do i model this without violating referential integrity?

E.G Tables:

    create table Tour(
    tourID int primary key,
    ...//
    );

    create table Gig(
    gigID int primary key,
    tourID int,
    ...//
    foreign key(tourID) references Tour.tourID
    );

PS: Newbie here

Best Answer

Just make the Tour optional? The below code is for SQL Server, but you should be able to port this to other flavors of SQL. Essentially, the TourID column can be nullable, indicating the Gig is not part of a Tour.

CREATE TABLE dbo.Bands
(
    BandID INT NOT NULL
        PRIMARY KEY CLUSTERED
    , BandName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Tours
(
    TourID INT NOT NULL
        PRIMARY KEY CLUSTERED
    , TourName VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.Gigs
(
    GigID INT NOT NULL
    , GigName VARCHAR(100) NOT NULL
    , GiGDate DATETIME NOT NULL
    , BandID INT NOT NULL
        FOREIGN KEY
        REFERENCES dbo.Bands(BandID)
    , TourID INT NULL
        FOREIGN KEY 
        REFERENCES dbo.Tours(TourID)
);

This design will also allow multiple bands to take part in any given tour, thereby providing a mechanism for headliners and opening acts, or multi-stage events, etc.

Filling out the example from above with some data:

INSERT INTO dbo.Bands (BandID, BandName)
VALUES (1, 'Past the Perimeter');

INSERT INTO dbo.Tours (TourID, TourName)
VALUES (1, 'Taking it to the City');

INSERT INTO dbo.Gigs (GigID, GigName, GiGDate, BandID, TourID)
VALUES (1, 'Tour Date 1', '2016-05-27T21:30:00', 1, 1);

INSERT INTO dbo.Gigs (GigID, GigName, GiGDate, BandID, TourID)
VALUES (2, 'Standalone Gig', '2016-05-28T21:30:00', 1, NULL);

SELECT b.BandName
    , g.GigName
    , g.GiGDate
    , t.TourName
FROM dbo.Bands b
    INNER JOIN dbo.Gigs g ON b.BandID = g.BandID
    LEFT JOIN dbo.Tours t ON g.TourID = t.TourID
ORDER BY g.GiGDate;

enter image description here

Related Question