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.
TableBId1 = 100
andTableBId2 = 200
is different thanTableBId1 = 200
andTableBId2 = 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