Both Primary and Foreign Key Needed on Table

database-designforeign key

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?

Best Answer

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)