id name favourite_foods
1 Sam Curry,Steak,Cheese
2 Lucy Chicken,Burgers,Apples
the table fail to 1NF ,so i separate out into two tables,
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
the Primary Key of table "person_favorite_foods" is id,but in "persons" table. id also the PK.is it ok?
the primary key
Moderator: General Moderators
Re: the primary key
You're misunderstanding the concept of primary keys a bit. What you have there is good, but 'id' in person_favorite_foods is not the primary key, it's a foreign key. For what it's worth, I wouldn't call the key in person_favorite_foods 'id'. I'd call it 'person_id'. That's more meaningful - it's obvious what it is. 'id' could be anything.
-
runeveryday315
- Forum Newbie
- Posts: 3
- Joined: Tue Sep 08, 2009 8:08 am
Re: the primary key
according to you said, there is no primary key in table "person_favorite_foods ",this is fail to 1 normal form.the table fit for 1 NF must have a primary key. am i wrong?onion2k wrote:You're misunderstanding the concept of primary keys a bit. What you have there is good, but 'id' in person_favorite_foods is not the primary key, it's a foreign key. For what it's worth, I wouldn't call the key in person_favorite_foods 'id'. I'd call it 'person_id'. That's more meaningful - it's obvious what it is. 'id' could be anything.
Re: the primary key
The primary key in your second table could be a compound key, the concatenation of the 2 fields (which would then be unique, which is the requirement for a primary key). Or you could assign another field as a primary key, for example, an auto-increment field. There is one and only one prime requirement for a primary key, and that is that it is unique in the table in which it is the primary key. That's its whole purpose: to be able to identify every row with no ambiguity.