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 the开发者_开发技巧re. 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

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:

• you can have composite keys
• surrogate keys may be added after both conceptual and logical design: you have added them up front which is bad. They are added because of the RDBMS performance, not at design time
• have you read several sources on 1NF?
• start with plain english and some facts. Which is what ORM does with verbalisation.

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. ...

I'd use some more tables for this, to remove duplication for customers, orders, toppings and pizze:

Table: Customer

   Customer_id
    Customer_name
    Customer_name
    Phone
    Promotion
    Blacklist Y or N
    Customer_address
    ZIP_code
    City
    E_mail

Table: Order

Order_id
Order_date
Customer_id
Delivery_zone
Deliveryguy_id
Deliveryguy_name
Delivery Y or N

Table: Order_Details

Order_ID (FK on Order)
Pizza_ID (FK on Pizza)
Amount

Table: Pizza

Pizza_id
Pizza_name
Size
Pizza_price

Table: Topping

Topping_id
Topping_name
Topping_prijs
Availabitly

Table: Pizza_Topping

Pizza_ID
Topping_ID

Pizza_topping and Order_details are so-called interselection tables ("helper" tables for modelling a m:n relationship between two tables).

Now suppose we have just one pizza, some toppings and our customer Billy Smith orders 2 quattro stagione pizze - our tables will contain this content:

Pizza(Pizza_ID, Pizza_name, Pizza_price)

  1 Quattro stagioni 12€

Topping(Topping_id, topping_name, topping_price)

  1 Mozzarrella 0,50€
  2 Prosciutto 0,70€
  3 Salami 0,50€

Pizza_Topping(Pizza_ID, Topping_ID)

 1 1
 1 3

(here, a quattro stagioni pizza contains only Mozzarrella and Salami).

Order(order_ID, Customer_name - rest omitted)

1 Billy Smith

Order_Details(order_id, Pizza_id, amount)

1 1 2  

I've removed delivery ID, since for me, there is no distinction between an Order and a delivery - or do you support partial deliveries?

On 1NF, from wikipedia, quoting Date:

According to Date's definition of 1NF, a table is in 1NF if and only if it is "isomorphic to some relation", which means, specifically, that it satisfies the following five conditions:

• There's no top-to-bottom ordering to the rows.
• There's no left-to-right ordering to the columns.
• There are no duplicate rows.
• Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

• All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

  —Chris Date, "What First Normal Form Really Means", pp. 127–8[4]

First two are guaranteed in any modern RDBMS.

Duplicate rows are possible in modern RDBMS - however, only if you don't have primary keys (or other unique constraints).

The fourth one is the hardest one (and depends on the semantics of your model) - for example your field Customer_address might be breaking 1NF. Might be, because if you make a contract with yourself (and any potential user of the system) that you will always look at the address as a whole and will not want to separate street name, street number and or floor, you could still claim that 1NF is not broken.

It would be more proper to break the customer address, but there are complexities there with which you would then need to address and which might bring no benefit (provided that you will never have to look a the sub-atomic part of the address line).

The fifth one is broken by some modern RDBMs, however the real importance is that your model nor system should depend on hidden elements, which is normally true - even if your RDBMS uses OIDs internally for certain operations, unless you start to use them for non-administrative, non-maintenance tasks, you can consider it not breaking the 1NF.

The strengths of relational databases come from separating information into different tables. One useful way of looking at tables is first to identify as entity tables those concepts which are relatively permanent (in your case, probably Pizza, Customer, Topping, Deliveryguy). Then you think about the relations between them (in your case, Order, Delivery ). The relational tables link together the entity tables by having foreign keys pointing to the relevant entities: an Order has foreign keys to Customer, Pizza, Topping); a Delivery has foreign keys to Deliveryguy and Order. And, yes, relations can link relations, not just entities.

Only in such a context can you achieve anything like normalization. Tossing a bunch of attributes into one singular table does not make your database relational in any meaningful sense.