Someone recently asked this question:

There are two tables: Deal and DealCategories. One deal can have many deal categories.

So the proper way should be to make a table called DealCategories with the following structure:

 DealCategoryId (PK)
 DealId (FK)
 DealCategoryId (FK)

cont ...

Can someone explain why DealCategories table has

DealCategoryId (FK)

on it?

2 Answers 2


That part did not make sense to me either. The OP (of that question) states:

"One deal can have many deal categories."

I assume that there can also be many deals in a deal category. So, the "proper" way to would be a DealCategory table:

DealCategoryId (PK)

and a DealInCategory many-to-many table, with:

DealId (FK to Deal)
DealCategoryId (FK to DealCategory)
PK: (DealId, DealCategoryId)
  • Would it not suffice to having just DealCategory with DealID(FK to Deal) and DealCategory(PK)? EDIT: Nevermind, you're creating a table to define unique DealCategories. I understand now. May 14, 2012 at 15:15
  • If you don't want to store anything else about DealCategories, except the relation to Deals, yes, that would suffice. May 14, 2012 at 15:24

For a hierarchy.

If you have subcategories, then you need to make sure the parent category of the current record actually exists.

You'll need to actually change the name for the field to make it valid, though.

  • after thinking for a couple of minutes make sense, but what about if you want to add another sub category?, what happen with the Third normal form, "Every non-prime attribute is non-transitively dependent on every candidate key in the table" (en.wikipedia.org/wiki/Database_normalization)
    – jcho360
    May 14, 2012 at 14:17
  • You can keep nesting with this format - You just make it a subcategory of a subcategory and it won't require a change to your structure at all.
    – JNK
    May 14, 2012 at 14:21
  • @JNK2, can you provide me any example? because I don't see it how to do it that way, if I add subcategory as PK I will have to add another field to difference category from subcategory.. is it not better to have separate tables???, thanks JNK2
    – jcho360
    May 14, 2012 at 14:29
  • You don't need a subcategory as a PK. CategoryA is the parent, CategoryB is a subcat of CategoryA, and CategoryC is a subcat of CategoryB.
    – JNK
    May 14, 2012 at 14:30
  • I understand that, but where are you going to place CategoryC?,in a new row?, or are you going to create a new column (this doesnt' make sense)?.
    – jcho360
    May 14, 2012 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.