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;
