It looks to me as though child_1 and child_2 are two instances of a single entity type.
If so, then you have a (second) relationship such that
- Every parent must have one and only one favourite child
- Every child may be the favourite of zero or more parents.
The (first) relationship being
- A parent may have zero or more children (unless, of course you have defined "parent" such that a child is a pre-requisite of being a parent ;-))
- A child must have exactly two parents (under basic human biology ;-))
Your first relationship may, of course, be different depending on the context.
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;
Best Answer
If I understand your question correctly, your existing "parent" table has a column that contains a string that represents the ID values from a "child" table, and those ID values are in a meaningful order. The question, then, is how to move those parent-child relationship representations out of a string with meaningful order, and into a more normalised database design.
If this is the goal, then I suggest your second design (DB#2), with a minor change, might work best for you.
In summary, a single new table can represent the relationships between child and parent records, with one column for each of those foreign keys. (You already have this in the design). The order of those relationships can be represented in an additional column. (You already have this in the design). The only change I would make is to remove the ID (first) column - there is no need for it.
If a child may only be represented once in relationship with its parent, then make your primary key across (idParent, idChild). If a child can be represented many times against its parent (ie. because it can occupy multiple places in the ordering of the relationships), then make the primary key across all three columns (idParent, idChild, priority).
Per your design, the parent table can drop its obj#3 column (ie. the one that currently contains all the strings).
I see no reason to introduce an extra table to represent the ordering / priorities.
This design allows for two levels in your hierarchy: parent and child. If you have a requirement for multiple levels (such as a person belongs to a department which belongs to an organisation), the question becomes more complex and there are some interesting articles out there on how to model such open-ended hierarchies. Otherwise, with the information presented, I recommend database #2 and, as mentioned, the first column is not required.