Refactoring/normalization - but almost empty table I normalized a legacy DB into this structure:


But I'm not sure if it is correctly normalized. I don't feel very comfortable with the almost empty filters table. 

The requirements


A group contains several items.
A filter organizes how a item is "displayed".
Multiple terms are defining a filter.
Different groups may contain the same item, but different filter.
A filter cam have no terms.


Not really important requirements


A filter might be used several times.


Btw. the diagram was created using http://cakeapp.com.
Honestly I have several questions about the requirements.  For example: Can a group contain the same item under different filters?  Can a group contain the same item under the same filter? Can a term be used on multiple filters?

Based on what you have above here is what I would create. Starting with the easy part you have a term table and a filter table.  A filter can have multiple terms, or none.  The one-to-many relationship here handles the Multiple terms are defining a filter and A filter cam have no terms requirements.  Next the group_item_filter table joining the group, item and filter tables creating a many-to-many-to-many relationship.  You will want to put a unique index on this table depending on the questions I have above.  My guess is that a group can have an item only once so the unique index would be (group_id, item_id).  This table allows for the requirements Different groups may contain the same item, but different filter and A filter might be used several times and A group contains several items.

I wouldn't worry about the all but empty filter table.  It still represents a logical entity regardless of weather or not it has a description or any other field.  For that mater you never know, you may decide to add a description later, or any number of other fields depending on the real world application.

Also I realize the table group_item_filter sounds a bit funny and you could break it down into two tables item_filter and group__item_filter but honestly it doesn't work as well.  This way you can create unique indexes to further define the business rules without some of the restrictions that breaking it up will cause.  



EDIT: 4/10/2013 9:24PM

Based on the comments below I have another option for you.  In this one I'm breaking up the 3 way cross reference table.  First there is a cross reference table between the group and the item tables (group_item) to support the requirement A group contains several items.  Next there is a second cross reference table that joins the group_item cross reference table with the filter table.  This supports the requirements Different groups may contain the same item, but different filter and A filter might be used several times.  The benefit here is that you don't have any unusual structures (the 3 way cross reference table) but still support the same requirements.  You also have an easy way to logically support a requirement where an item may only be used once per group by putting a unique key on the (group_id, item_id) combination in the group_item table.