To normalize or not to normalize? (10 fields)

database-designnormalizationschema

I'm helping someone to design a database and for a specific case I'm not sure.

I have 10 fields – this will never change – that will always be filled and used in every query that could be coming up. Always.

So I came up with 2 solutions, normalized and not normalized

db

I would have to query the first solution like this;

    select n.Id
         , Type1Selector1.Info as Type1Selector1Info
         , Type1Selector2.Info as Type1Selector2Info
         , Type1Selector3.Info as Type1Selector3Info
         , Type1Selector4.Info as Type1Selector4Info
         , Type1Selector5.Info as Type1Selector5Info
         , Type2Selector1.Info as Type2Selector1Info
         , Type2Selector2.Info as Type2Selector2Info
         , Type2Selector3.Info as Type2Selector3Info
         , Type2Selector4.Info as Type2Selector4Info
         , Type2Selector5.Info as Type2Selector5Info

    from dbo.OptionOneMainTable n
         inner join dbo.OptionOneSecondTable Type1Selector1 on
                    Type1Selector1.Id = n.Id
                and Type1Selector1.Type = 1
                and Type1Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type1Selector2 on
                    Type1Selector2.Id = n.Id
                and Type1Selector2.Type = 1
                and Type1Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type1Selector3 on
                    Type1Selector3.Id = n.Id
                and Type1Selector3.Type = 1
                and Type1Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type1Selector4 on
                    Type1Selector4.Id = n.Id
                and Type1Selector4.Type = 1
                and Type1Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type1Selector5 on
                    Type1Selector5.Id = n.Id
                and Type1Selector5.Type = 1
                and Type1Selector5.Selector = 5
         inner join dbo.OptionOneSecondTable Type2Selector1 on
                    Type2Selector1.Id = n.Id
                and Type2Selector1.Type = 2
                and Type2Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type2Selector2 on
                    Type2Selector2.Id = n.Id
                and Type2Selector2.Type = 2
                and Type2Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type2Selector3 on
                    Type2Selector3.Id = n.Id
                and Type2Selector3.Type = 2
                and Type2Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type2Selector4 on
                    Type2Selector4.Id = n.Id
                and Type2Selector4.Type = 2
                and Type2Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type2Selector5 on
                    Type2Selector5.Id = n.Id
                and Type2Selector5.Type = 2
                and Type2Selector5.Selector = 5

and obviously the second solution would be

    select Id
         , Type1Selector1Info
         , Type1Selector2Info
         , Type1Selector3Info
         , Type1Selector4Info
         , Type1Selector5Info
         , Type2Selector1Info
         , Type2Selector2Info
         , Type2Selector3Info
         , Type2Selector4Info
         , Type2Selector5Info

    from dbo.OptionTwoMainTable

I'm not alone doing this database and I would personally take the second solution.

Should I try to convince the other person to go for solution two? He wants to go for solution one.

Best Answer

If the fields are always going to have a value and are always read as a group, then option 1 is normalized.

The column names could use some work, but I assume they have been obfuscated.

There is a third option which is to split the two types out into separate tables. This would be appropriate if either type grouping was optional and appear separately.

Option 2 is appropriate where the fields are independent of the id (Primary key). From your description, this is not the case.

Even if you do model the two types as separate tables in the design model, one of the implementations of subtypes is to incorporate them in the main table using nullable columns.

Related Question