Table design – parent with always exactly 1 or 2 children

database-designschema

Lets say I have the following existing tables (both with a primary key column named Id):

TableA
TableB

Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 records from TableB. The relationship here is always either 1 or 2 records and will never change, but the order of these 2 relations does matter. I'm calling this new table Groups but I can't decide on the proper way to design it.

Option 1 – flat

Groups
----------------------------
Id INT NOT NULL PRIMARY KEY,
TableAId INT NOT NULL,
TableBId1 INT NOT NULL,
TableBId2 INT NULL

Option 2 – details table

Groups                         GroupDetails
----------------------------   --------------
Id INT NOT NULL PRIMARY KEY,   GroupId INT NOT NULL,
TableAId INT NOT NULL          TableBId INT NOT NULL,
                               Sort TINYINT NOT NULL

Option 1 will make it easier to retrieve records since it reduces a join, but the column TableBId2 could potentially be null in some cases.

Option 2 will require a join, but it's more normalized and just seems like the "proper" way to design this table.

However, in this case I'm leaning towards breaking the normalization principles in favor of a slight optimization, because like I said, each Group record will always have at least 1 relation to TableB, and at most, a 2nd relation. I'm struggling to make a decision on this, so I'm hoping someone can give their opinion on it.

Best Answer

The second option seems a bit odd. Why do you need the GroupDetails table instead of having a regular link table with 1 or 2 rows.

Groups                      
----------------------------
Id INT NOT NULL PRIMARY KEY 
TableAId INT NOT NULL       
TableBId INT NOT NULL
Sort TINYINT NOT NULL

In any case if you don't need to support more than two links, I would choose the first option. There's nothing wrong with having a null value in a column, so that's not an issue. You also get rid of the cumbersome sort column, which for 2 values is pretty much overkill anyway.

Related Question