# Table design - parent with always exactly 1 or 2 children

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.

• The relationship here is always either 1 or 2 records Are these records equal? Can they be swapped? Aug 21, 2019 at 13:26
• No, they can't be swapped, the order of these does matter. I've updated the question with those details. Aug 21, 2019 at 13:29
• most queries will always need to pull both records. the relationship between the 2 records is the same, but the order of them matters, i.e. TableBId1 = 100 and TableBId2 = 200 is different than TableBId1 = 200 and TableBId2 = 100. But both option 1 and option 2 would require 3 joins. Option 1: Groups -> TableB (Id1) -> TableB (Id2) and Option 2: Groups -> GroupDetails -> TableB Aug 22, 2019 at 13:32
• Is EVERY record in TableA related to a Group, or do some TableA records have no Group, and therefore no relationship to TableB? Aug 22, 2019 at 20:14

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.