3

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?

Improve this question

2 Answers 2

Reset to default
4

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:

DealCategory
--------------
DealCategoryId (PK)

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

DealInCategory
--------------
DealId (FK to Deal)
DealCategoryId (FK to DealCategory)
PK: (DealId, DealCategoryId)
Improve this answer
2
  • 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.
    – Michael Capobianco
    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.
    – ypercubeᵀᴹ
    May 14, 2012 at 15:24
3

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.

Improve this answer
6
  • 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 that you have read and understand our privacy policy and code of conduct.

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