Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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)
share|improve this answer
    
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 '12 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 '12 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.

share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 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 '12 at 14:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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