0

enter image description here

I asked this question, and someone suggested this type of schema. I'm not familiar with how super/subtypes work. Can you show me a TSQL example of how I would create this database?

Another problem I'm having trouble wrapping my head around, is given an order, and a collection of items in that order, how would I know if an item is a Pizza, Beverage or Side dish?

Thank you.

Community
  • 1
  • 1

2 Answers2

2

One way is to use Super Keys to constrain subtypes

  • Item would have a TypeID column (lookup to a new table ItemType)
  • New ItemType 1=Pizza, 2=Sides, 3=Beverage etc
  • There is a new unique constraint on Item with current PK column(s) + TypeID
  • Each table Pizza, Beverage, Sides has a typeID column with a CHECK constraint to restrict entries in the table to one of Pizza, Sides, Beverage. So 1 for Pizza etc.
  • The FK from Pizza, Beverage, Sides to Item is the same as the new unique constraint above

This answers your second question too

gbn
  • 422,506
  • 82
  • 585
  • 676
1

Tables:

ITEM_SUPER_TYPE

  • ITEM_SUPER_TYPE_ID (pk, IDENTITY)
  • DESCRIPTION

ITEM_SUB_TYPE

  • ITEM_SUB_TYPE_ID (pk, IDENTITY)
  • ITEM_SUPER_TYPE_ID (fk to ITEM_SUPER_TYPE.ITEM_SUPER_TYPE_ID)
  • DESCRIPTION

ITEM

  • ITEM_ID
  • ITEM_SUB_TYPE_ID (fk to ITEM_SUB_TYPE.ITEM_SUB_TYPE_ID)

This way, the super type can be inferred from the subtype.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • But a single row in the supertype might be related to a row in every subtype table. The answer and comments to this SO question explain how to avoid that. http://stackoverflow.com/questions/4969133/database-design-problem – Mike Sherrill 'Cat Recall' Mar 28 '11 at 00:41