how to make this table fit 1nF

[runeveryday]

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


i konw it has duplicative columns,i use the following to eliminate,

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


but the values are not atomic,how to make this table can fit 1NF.
thanks in advance.

[bazz]

two tables. you have a one-to-many relationship so you need more than one table.


people
person_id |name|

foods
id | person_id |favourite_food


person_id is a foreign key in table foods as it references the person_id in the people table

look up 'foreign key constraints' to see how 'referential integrity' is maintained by constraints.

bazz

[runeveryday]

foods
id | person_id |favourite_food

can the table write this
foods
id | person_id |favourite_food
1 | 1 | Curry,Steak,Cheese
2 | 2 | Chicken,Burgers,Apples

[bazz]

not if you are trying to normalise the database. I can't recall the term for it but, you should never use comma-separated data in one field. Makes queries less efficient and searching (for example), to see who likes which foods, a nightmare.

if you want it to be normalised, the way to do it is shown in my last post.

bazz

[Old Pedant]

Delimited lists in single fields are probably the most common--and worst--mistake that DB newbies make. Don't do it. Even if you think your case is the exception. I'll bet you a latte or two it's not.

[Coyote6]

foods

can the table write this
foods
id | person_id |favourite_food
1 | 1 | Curry,Steak,Cheese
2 | 2 | Chicken,Burgers,Apples

What this should look like then is:

people
person_id | name
1 | Bazz
2 | Old Pedant
:thumbsup:

foods
id | person_id | favourite_food
1 | 1 | Curry
2 | 1 | Steak
3 | 1 | Cheese
4 | 2 | Chicken
5 | 2 | Burgers
6 | 2 | Apples

So when you query your database with the query like this:

SELECT `favourite_food` FROM `foods` WHERE `person_id`='1';
SELECT `favourite_food` FROM `foods` WHERE `person_id`='2';


You get:
person_id = 1
favourite_food
Curry
Steak
Cheese

person_id = 2
favourite_food
Chicken
Burgers
Apples

[Old Pedant]

Okay, I don't get it. WHY do so many MySQL users do

SELECT `favourite_food` FROM `foods` WHERE `person_id`='1';

instead of

SELECT favourite_food FROM foods WHERE person_id=1;

???

I can sort of understand using the `...` escape characters. Among other things, when MySQL dumps tables, etc., it always uses those. And clearly they don't hurt anything. The compiler uses or ignores them as it sees fit.

But why the apostrophes around what is *CLEARLY* an integer value 1??? All that does is force MySQL to do a runtime conversion from the string '1' to the integer value 1. Yes, yes, I know, it would have to do so anyway, since the SQL *is* a string. But presumably it can then do it at compiile time instead of runtime. (And I know this is a maybe irrelevant point, if you are strctly a MySQL user, but some other DBs will choke on the wrong data type, give you a "type mismatch" error or similar when you use the apostrophes where they don't belong.)

I've posted in various other DB forums, and it seems that the MySQL forums are the only ones where I see this practice. Does it come from using PHP???

**********

p.s.: Please, Coyote, I'm not picking on you. It's just you're one of the more experienced users here (even though new to these forums), from what I've seen. So hoping you have an answer.

[Old Pedant]

I'm going to live up to my screen name and go really pedantic on this one.

*PROBABLY* the right way to organize this data is as follows:

people
person_id | name
1 | Bazz
2 | Old Pedant
3 | Coyote6
4 | RunEveryDay

foods
food_id | name
1 | Curry
2 | Steak
3 | Cheese
4 | Chicken
5 | Burgers
6 | Apples

favoriteFoods
person_id | food_id
1 | 1
1 | 2
1 | 3
2 | 4
2 | 5
2 | 6
3 | 2
3 | 3
3 | 4
3 | 6
4 | 2



You see? You don't repeat *EITHER* the person names *OR* the food names. And of course any person can be associate with any number of foods. And vice versa.

Just for example, try to answer a query such as
"How many people like 2 or more of the same foods?"
with any other DB organization. Oh, it can be done. But you'll work harder.

[runeveryday]

Old Pedant
can i change table foods ,favoriteFoods into one table.like this

id | name
1 | Curry
2 | Steak
3 | Cheese
4 | Chicken
2 | Burgers
3 | Apples

thank you!

[bazz]

re-read your post! so my answer is totally different. :eek:

If you know there will only be one food type in table foods, you do not need a numerial PK.

foods could be


food
| Curry
| Steak
| Cheese
| Chicken
| Burgers
| Apples


Then your third joining table [aka many-to-many table] could be as you say.

look up FOREIGN KEY CONSTRAINTS. if you were ever to change a food in the table foods, then it would change automatically in favouriteFoods or not, or set null, depending on your constraint.

bazz

[oracleguy]

I'm going to live up to my screen name and go really pedantic on this one.

*PROBABLY* the right way to organize this data is as follows:

people
person_id | name
1 | Bazz
2 | Old Pedant
3 | Coyote6
4 | RunEveryDay

foods
food_id | name
1 | Curry
2 | Steak
3 | Cheese
4 | Chicken
5 | Burgers
6 | Apples

favoriteFoods
person_id | food_id
1 | 1
1 | 2
1 | 3
2 | 4
2 | 5
2 | 6
3 | 2
3 | 3
3 | 4
3 | 6
4 | 2



You see? You don't repeat *EITHER* the person names *OR* the food names. And of course any person can be associate with any number of foods. And vice versa.

Just for example, try to answer a query such as
"How many people like 2 or more of the same foods?"
with any other DB organization. Oh, it can be done. But you'll work harder.

This is exactly how it should be done. This is how I would design it. That way you don't waste space repeating data and increases the data integrity since you don't have to worry about a misspelling screwing it up. And it makes it easier in a program or web form. You can populate the choices very easily with the foods table.

Old Pedant
can i change table foods ,favoriteFoods into one table.like this

id | name
1 | Curry
2 | Steak
3 | Cheese
4 | Chicken
2 | Burgers
3 | Apples

thank you!

No, the IDs have to be unique since it is the primary key. In this case, the id column of the foods table would be the an auto incrementing integer so you would never have duplicates.

[Old Pedant]

But Bazz's answer is also technically correct. So long as you indeed have that table and the foodname value is the primary key.

We tend to not do this because an integer PK becomes an integer FK and the integer is smaller and faster for doing computer operations than a string would be. VARCHAR, especially, is about the worst choice for a key if you are worried about performance. (I can remember working with early DBs where we might purposely create an extra column as a CHAR field, just to avoid the overhead of VARCHAR comparisons...but that was on much slower and smaller machines than are in use today.) Anyway, those are performance considerations, not Normal Form correctness considerations.

From my own standpoint, I don't understand why you would *want* to avoid an integer PK. But each to his/her own.

[runeveryday]

hi oracleguy,you said "the IDs have to be unique since it is the primary key." but in table "favoriteFoods ", in person_id and food_id,there is also some duplicates.eg:
1
1
which is a PK ?

[bazz]

in table people and table favourites, the id is a PK. in the third table, they are foreign keys because they reference the PK in the first two tables.

what you should have is a unique index on the columns in the third table both ways around.

pseudo code (cz I am too tired to make sure it's correct

index_1_ix (person_id,food_id)
index_2_ix (food_id,person_id)


That would ensure that which ever way you query joined on that table, it wouldn't have to do a full scan since it can use the index.

bazz

[Coyote6]

Okay, I don't get it. WHY do so many MySQL users do

SELECT `favourite_food` FROM `foods` WHERE `person_id`='1';

instead of

SELECT favourite_food FROM foods WHERE person_id=1;

???

I can sort of understand using the `...` escape characters. Among other things, when MySQL dumps tables, etc., it always uses those. And clearly they don't hurt anything. The compiler uses or ignores them as it sees fit.

But why the apostrophes around what is *CLEARLY* an integer value 1??? All that does is force MySQL to do a runtime conversion from the string '1' to the integer value 1. Yes, yes, I know, it would have to do so anyway, since the SQL *is* a string. But presumably it can then do it at compiile time instead of runtime. (And I know this is a maybe irrelevant point, if you are strctly a MySQL user, but some other DBs will choke on the wrong data type, give you a "type mismatch" error or similar when you use the apostrophes where they don't belong.)

I've posted in various other DB forums, and it seems that the MySQL forums are the only ones where I see this practice. Does it come from using PHP???

**********

p.s.: Please, Coyote, I'm not picking on you. It's just you're one of the more experienced users here (even though new to these forums), from what I've seen. So hoping you have an answer.

No biggie. The reason I started doing it is because when using php to write a variable directly into a query and without using a procedure it can sometimes cause an error.


// With an integer.
$var = 1

// Like you said no big deal if it is always an integer.
$q = "SELECT * FROM Table WHERE field_1=$var";

// Now with a string.
$var = 'this_is_a_value_not_a_field';

// You get an error because it starts looking for the name of a field in the table instead of matching the string.
$q = "SELECT * FROM Table WHERE field_1=$var";
//$q = "SELECT * FROM Table WHERE field_1=this_is_a_value_not_a_field";

// If a single quote is used to enclose them then it will work for both an integer or string, which is why you probably see it so much with php.
$q = "SELECT * FROM Table WHERE field_1='$var'";


As for the putting the marks around the `field` I just started using that because it was easier to tell where my db fields and tables were in comparison to my inputted data when I started doing more complex procedures. Which if you are using a stored procedure then you most likely would not be using the single quote ' at all.

Sorry for the late explanation I've been sick the last couple of days and still partially am. Not fun at all.

[bazz]

what?? a coyote with swine flu?? gulp! :D

get well soon.

[Coyote6]

what?? a coyote with swine flu?? gulp! :D

get well soon.

:eek: lol that's funny! Just sinus crap. No swine flu here... I got checked so I know... And knowing is half the battle! lol sorry little GI Joe throwback there. :cool:

Thanks Man.