0

I'm insecure about the way i'm thinking when I'm normalising. I'm designing a database for an fictional online pizza shop.

consider a table with a concatenated key being order_nr and pizza_article_nr.

I'm stuck with the pizza toppings. I'm thinking that taken litterally, they don't rely on the pizza since they technically speaking can exist on their own. Yet in reality they're always connected to a pizza. So then do they exist on their own so that I will deal with them in 3NF or does the column 'toppings' fail 2NF because it does rely on the pizza in practical reality?

Immers
  • 343
  • 1
  • 6
  • 22
  • 1
    possible duplicate of [Help designing a database schema for a pizza store.](http://stackoverflow.com/questions/5419931/help-designing-a-database-schema-for-a-pizza-store) – Ronnis Apr 07 '11 at 18:14
  • I guess pizza shops are pretty good exercise for DB architects... – cHao Apr 07 '11 at 18:27

2 Answers2

0

"Yet in reality they're always connected to a pizza."

Are they ?

The business of a pizza shop is, imho, precisely to have toppings in stock that are not yet "connected to a pizza", and this precisely for the purpose of being able to create a pizza.

What you're saying is equivalent to "engines are always connected to a car". That statement may be true once the car leaves the production hall, but it most certainly is not true as long as the engine is waiting in the production hall's stock/supply to get "connected to a car".

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • Exactly that's what I mean that in reality the toppings are on the pizza like an engine is always in the car but that doesn't matter if you're thinking about normalising, am I following you correctly then? – Immers Apr 11 '11 at 08:01
0

The source of your confusion is that you are seeing keys in more than one place and you're thinking that it must be redundancy. The fact is that in normalization you need to ignore the psuedo-redundancy in the keys. This is not real redundancy but merely repetition of information. The repetition is there for a reason, namely to indicate the relationship between entities.

If you have a table for toppings that are available, i.e. the primary key is topping_id, then a table that tells you which topping is on which pizza is 3NF. If you don't have a lookup table for toppings and instead put the topping name in your pizza composition table, then I think a lot of people would say you're violating 2NF. They would be right if topping names are not immutable. If the topping names happen to be immutable then there's an argument to say that the topping name is your primary key to an implicit topping table. However, as a matter of best practice, it's good to have meaningless keys in general - unless you can come up with a really good reason to use a meaningful key on a case by case basis. Therefore avoid using topping name in your pizza composition table.

Since you can often order more than one pizza at a time (I cut code and have two teenage sons, so I speak from experience) your schema should probably be along these lines:

ORDER:
  order_id (PK)
, date_taken
, deliver_to (or FK to a CUSTOMER table if you're being ambitious)

PIZZA:
  pizza_id (PK)
, order_id (FK)
, size

TOPPING:
  topping_id (PK)
, topping_name

PIZZA_COMPOSITION:
, pizza_id (PK, FK)
, topping_id (PK, FK)
, quantity (My kids insist on double cheese)
, coverage (One likes half plain cheese...)

This schema is 3NF because the only thing that appears in more than one place is a foreign key.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • Wow. I feel like I need to understand what you are saying very badly in order to get this part of webdevelopment handled. But I don't. The first sentence already messes with my head. How do you mean I'm seeking keys in more than one place? What would be those places? – Immers Apr 11 '11 at 08:02
  • another question (going back to 1NF): why wouldn't there be a table with 3 keys in it being order_id, pizza_id and topping_id? bacause when I have just an order_id with a pizza_id do I know what toppings are on that pizza? I don't think so because within the same order there might be to pizzas of the same type with different toppings. Note that with toppings I mean the same as extra's. – Immers Apr 11 '11 at 09:34
  • By keys in more than one place I mean multiple tables containing the key data (either PK or FK). A table with 3 keys (order, pizza, topping) would violate 2NF because it contains partial dependencies. Pizza depends on order, insofar as one pizza can't be part of two or more orders. The same goes for toppings. To be normalized you need to have one table for pizzas and another for which topppings are on each pizza in addition to a table for orders. – Joel Brown Apr 11 '11 at 12:37