does this table fit the 1nf

[runeveryday]

table users
userId name company company_address url
1 Joe ABC 1 Work Lane abc.com
1 Joe ABC 1 Work Lane xyz.com
2 Jill XYZ 1 Job Street abc.com
2 Jill XYZ 1 Job Street xyz.com

i saw an example about the database First Normal Form the author said the above table is in the First Normal Form. i don't agree with him.because there are lots of repeating in the table.is this right?

[r937]

no, it is not right

repeating stuff is fine

1NF means two things -- first, the table must have a primary key, and second, each column must contain an atomic value

the PK is not obvious in this case, but it is there

[runeveryday]

First Normal Form (1NF) sets the very basic rules for an organized database:

Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column (the primary key).

i saw on a site, "Eliminate duplicative columns from the same table. " this is wrong?

[SJH]

The key word in your example is columns. Duplicate rows, as in your table, are fine.

The example is telling you not to do stuff like:

id          name          favourite_food_1          favourite_food_2          favourite_food_3
1           Sam           Curry                     Steak                     Cheese
2           Lucy          Chicken                   Burgers                   Apples

In this case the favourite food columns should be refactored out into a separate table.

[r937]

Duplicate rows, as in your table, are fine.

actually, the "duplicate" rows would have to differ in at least one column value -- otherwise, a primary key is not possible, and therefore the table would fail 1NF

[runeveryday]

The key word in your example is columns. Duplicate rows, as in your table, are fine.

The example is telling you not to do stuff like:

id          name          favourite_food_1          favourite_food_2          favourite_food_3
1           Sam           Curry                     Steak                     Cheese
2           Lucy          Chicken                   Burgers                   Apples

In this case the favourite food columns should be refactored out into a separate table.

why the example is fail to 1NF?i see it suit for atomic attribute.

For a database to satisfy the First Normal Form, it must fulfill three requirements:
1. There should be no repeating columns containing the same kind of data.
2. All columns should contain a single value.
3. There should be a primary key to uniquely identify each row.

who can make an example to explain "There should be no repeating columns containing the same kind of data."
thank you!

[r937]

1. There should be no repeating columns containing the same kind of data.

favourite_food_1, favourite_food_2, favourite_food_3

same kind of data, yes?

[runeveryday]

bingo!i got it .
thank you very much. r937

[r937]

technically, that table is actually okay, although it certainly breaks the spirit and intent of 1NF

if, on the other hand, you had something like this --

id  name  favourite_foods
 1  Sam   Curry,Steak,Cheese
 2  Lucy  Chicken,Burgers,Apples

then that would fail 1NF because of rule 2, the values are not atomic

[runeveryday]

yeah,how to make this database fit for 1NF?separate some tables to avoid not atom?how to divide? thank you!

[r937]

yes, you would separate out into a new table --

persons
id name
1 Sam
2 Lucy

person_favorite_foods
id favourite_food
1 Curry
1 Steak
1 Cheese
2 Chicken
2 Burgers
2 Apples

[runeveryday]

got it!
thanx very much for u! hug!

[runeveryday]

oh,the Primary Key of table "person_favorite_foods" is id,but in "persons" table. id also the PK.is it ok?

[SJH]

The primary key of person_favourite_foods should be a composite key of the id and favourite_foods columns.

[runeveryday]

i know a little about composite key ,i will read some articles about it. do the following table fit for 1NF?

Title | ISBN |Price |Cust. name | Cust. address |Purch. date
PHP Cookbook |0596101015 |44.99 | Emma Brown 1565 Rainbow Road |Mar 03 2009

Dynamic HTML |0596527403 |59.99| Darren Ryder 4758 Emily Drive |Dec 19 2008

PHP and MySQL| 0596005436| 44.95| Earl B. Thurston 862 Gregory Lane |Jun 22 2009

PHP Cookbook| 0596101015 |44.99 | Darren Ryder 4758 Emily Drive| Dec 19 2008

[runeveryday]

i think it fail to 1 NF,because the "Cust. name " "Cust. address"are same kind of data.
is it right?

[SJH]

I *think* that fits 1NF but breaks 2NF in that the customers' details are not dependent on the primary key for the row (which in this case I would assume would be the ISBN).

[runeveryday]

in your above example,
favourite_food_1 favourite_food_2 are same kind of data.
why in this example "Cust. name " "Cust. address" aren't same kind of data.
i am very confused,how to recognize same kind of data?
thank you very much.

[SJH]

Test it by swapping the values in those fields round. Still make sense? Then you've got the same kind of data. Swap the customer name and address values around and there's clearly something amiss immediately

[runeveryday]

got it!thank you!hug!