database-designdatabase-normalizationfirst-normal-form

I keep messing up 1NF


For me the most understandable description of going about 1NF so far I found is ‘A primary key is a column (or group of columns) that uniquely identifies each row. ‘ on www.phlonx.com I understand that redundancy means per key there shouldn’t be more than 1 value to each row. More than 1 value would then be ‘redundant’. Right?

Still I manage to screw up 1 NF a lot of times. I posted a question for my online pizzashop http://foo.com pizzashop here

where I was confused about something in the second normal form only to notice I started off wrong in 1 NF. Right now I’m thinking that I need 3 keys in 1NF in order to uniquely identify each row. In this case, I’m finding that order_id, pizza_id, and topping_id will do that for me. So that’s 3 columns. Because if you want to know which particular pizza is which you need to know what order_id it has what type of pizza (pizza_id) and what topping is on there. If you know that, you can look up all the rest. Yet, from an answer to previous question this seems to be wrong, because topping_id goes to a different table which I don’t understand. Here’s the list of columns:

Order_id
Order_date
Customer_id
Customer_name
Phone
Promotion
Blacklist Y or N
Customer_address
ZIP_code
City
E_mail
Pizza_id
Pizza_name
Size
Pizza_price
Amount
Topping_id
Topping_name
Topping_prijs
Availabitly
Delivery_id
Delivery_zone
Deliveryguy_id
Deliveryguy_name
Delivery Y or N

Edit: I marked the id's for the first concatenated key in bold. They are only a list of columns, unnormalized. They're not 1 table or 3 tables or anything


Solution

  • use Object Role Modelling (say with NORMA) to capture your information about the design, press the button and it spits out SQL.

    This will be easier than having you going back and forth between 1NF, 2NF etc. An ORM design is guaranteed to be in 5NF.

    Some notes:

    So:

    1. A Customer has many pizzas (zero to n)
    2. A pizza has many toppings (zero to n)
    3. A customer has an address
    4. A pizza has a base
    5. ...