I'm relatively new to Database Design and I wanted to know the disadvantages to a particular decision I have made in the design of a database for a project. I don't think it will have an effect on the answer, but I am using SQL Server 2008 R2.
Project:
We have things called Bids
which contain Materials.
Materials can be grouped so I plan on having a MaterialGroup
table, However, Materials don't have to be apart of a group (they can be unassigned
if you will). So, instead of making a link-table or making the foreign key nullable I decided to make the first group in the database called unassigned
and instead of allowing Materials not be grouped I plan to place them in this group if not given a group.
Question: Are there any other disadvantages to expecting the first row of the table MaterialGroup
to be unassigned
(other than the case of someone failing to insert it there)? From both a practical and theoretical standpoint should I switch to a link-table (or another alternative)?
Best Answer
Having a group that is not a group may lead to reporting issues down the road. For instance, if you want to know the average number of Materials per Group, the results could easily be skewed by the overly large number of
unassigned
group members.I'd lean towards having a cross reference table that links
Groups
toMaterials
. So a table namedMaterialsGroupsXRef
that containsGroupID
andMaterialID
; anyMaterial
that is a member of aGroup
has a row in that table.Any
Materials
that are not a member of aGroup
would not show up in theMaterialsGroupsXRef
table. This allows you to easily identifyMaterials
that do not have a group. Likewise, you could easily identifyGroups
with noMaterials
.