Relying on expected data

Question

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)?

Answer 1

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 to Materials. So a table named MaterialsGroupsXRef that contains GroupID and MaterialID; any Material that is a member of a Group has a row in that table.

Any Materials that are not a member of a Group would not show up in the MaterialsGroupsXRef table. This allows you to easily identify Materials that do not have a group. Likewise, you could easily identify Groups with no Materials.