I normalized a legacy DB into this structure: enter image description here

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.

  • In your diagram, a group doesn't contain any items. Base your normalization on dependencies. – Mike Sherrill 'Cat Recall' Mar 21 '13 at 23:54
  • @MikeSherrill'Catcall' yes, but how else should I connect groups and items? And where should I place the filters? – powtac Mar 22 '13 at 8:16
  • The word "query" doesn't appear in the reqwuirements. What requirement does it fulfill? – Walter Mitty Apr 6 '13 at 14:38
  • query is just an attribute of an item. – powtac Apr 6 '13 at 16:33

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.

enter image description here

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.

enter image description here

  • No, a group should not contain the same folder under the different filter. – powtac Apr 10 '13 at 6:48
  • Then like I said above you are going to want a unique index on group_id, item_id for the group_item_filter table. – Kenneth Fisher Apr 10 '13 at 11:37
  • Is group_item_filter a "common" pattern? – powtac Apr 10 '13 at 12:08
  • 1
    A cross reference table to create a many-to-many relationship between two tables is very common. It's a bit more uncommon to create a 3 way cross reference table but I've certainly seen it before. It just really depends on your needs. – Kenneth Fisher Apr 10 '13 at 12:21
  • Thanks, principally this sounds good! I think my application framework can not handle it, have to check it. – powtac Apr 10 '13 at 12:30

I'm having a hard time with this diagram, since it seems to me that the filters table adds nothing to the model that an extra column on the items table wouldn't do.

Shouldn't the filter define a relationship between a term and an item, and then tie that relationship to a specific group ?

FilterGroups contains the relationship between Filters and Groups Filters contains the relationship between Items and Terms

That would make a whole lot more sense to me.

The diagram would then look something like this :

enter image description here

  • 1
    Unless I'm mistaken this doesn't work for the requirement Multiple terms are defining a filter. You are only allowing for a single term per filter. – Kenneth Fisher Apr 10 '13 at 3:25
  • You're right. It would be useful to have a TermsFilters table. That would add flexibility to the model – druzin Apr 10 '13 at 19:10

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.