NOt Code but theory help on which way to do...

[sir pannels]

hey.. ok i need some help.. not coding.... but how i would go about using the DB.
ok so i have some tables in the db.... one of them is user with al the users details in and some other ones with info that ties to them.. anyway what i need to add is some way to store "something that a user has"
for example "food".. i want to store all the different types of food with a description of each in a table for each user. so i can call it like
select * from table_name where id=$id
etc.... so whats the best way to do this... i was gona have just a table food and inwhich have 3 fields.. id, name, description .. but then you can only add one food to each user/id.
so is the only other way to have a entire table for each users food? if this is then what sorta load on the server if i had that.. is there a better way?
thanks
bit scetchy - hope it makes sense
cheers :)
P

[bcarl314]

Well, I'd normalize the table like this:

table users {
uID
fName
lName
...
}

table food {
fID // this is an auto incremented field and the primary key
uID // foreign key to users.uID
foodName
foodDesc
...
}

Then to get all the foods for a specific user, you use this query.
SELECT * FROM food, users WHERE food.uID=users.uID AND users.uID='myUserID';

[Kiwi]

The other way to do it, if you have a lot of 'foods' that are duplicated is to have The User table:
UserID
... user data ...

And a Food table:
FoodID
... food data ...

And a linking table, UserFood:
UserFoodID % a unique ID that you will probably hardly every use
UserFoodUser % the user key
UserFoodFood[/b] 5 the food key
... link data ...

To get a list of all food from a given user, you would use something like:
SELECT
User.UserName,
Food.FoodDesc
FROM
User AS u,
Food AS f,
UserFood AS l
WHERE
l.UserFoodUser = "id value" AND
l.UserFoodUser = u.UserID AND
l.UserFoodFood = f.FoodID

The advantage of this approach is that if you want a list of all the users who like a given food, you can do it, without having to hope that there are no minor differences in spelling or cell format between the different food types. It's a standard means of creating a many-to-many link.

[sir pannels]

thanks for that
its really help full :)
*runs off to try*
:thumbsup: