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 &quot;proper&quot; 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 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.