1

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? – Akina Aug 21 at 13:26
  • No, they can't be swapped, the order of these does matter. I've updated the question with those details. – jtate Aug 21 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 – jtate Aug 22 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? – John Rees Aug 22 at 20:14
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.