Looking for Design Suggestions

[Jonathan Wood]

I'm trying to decide the best table structure for a database and was hoping
some folks with more experience might have some suggestions.

I have the following tables: [Users], [Menus], [MenuDetails], and [Foods].
These tables define menu plans for each user.

The relationships are as follows:

[Foods]<--[MenuDetails]-->[Menus]-->[Users]

(There can be any number of menus per user, any number of menu details per
menu, and any number of menu details referencing the same food.)

This seems straight forward but the problem is that I now need to allow
Users to create their own foods, which can be used in their menus. I hate to
make the data structure more complex.

One thought is to store user foods in the main foods table. I could add a
UserID field to the foods table and keep it NULL for foods that are not
specific to a particular user.

I'm not sure how clear that is. I'm trying to figure out how much sense this
approach makes, particularly using NULL for UserIDs when a food is not
associated with a user. Also, it would be nice to keep the main food table
separate in case some bad code deletes all the foods during testing. But
adding another table for user foods just seems like it would make the query
a nightmare.

It'd be great if anyone could speak to this!

 

[RobinS]

Can you give some examples of the data? I'm not sure the diff between
MenuDetails and Foods, but I'll take a crack at it.

Foods should be a standalone table that is referenced by the other tables.
Assuming that MenuDetails is an entity like, say, "stroganoff" or "lunch#1",
that would be made up of many foods, you could do this:

Foods
foodID
description

MenuDetails
mdID
description

Menus
menuID
description

Users
userID
userName

MenuDetails/Foods
mdID
foodiD

UserMenus
userID
menuID

UserFoods
userID
foodID


I am assuming that foods, menus, menuitems, and users all have some kind of
description or name, and can be associated on a one-to-many or many-to-many
relationship with the other entities. This structure allows you to assign
any menu to any user, any menu-item to any menu, any food to any user, and
any food to any menu-item.

Does that make sense?

HTH,
RobinS.
GoldMail, Inc.

 

[Jonathan Wood]

Robin,

Can you give some examples of the data? I'm not sure the diff between
MenuDetails and Foods, but I'll take a crack at it.

Rather than contain the actual foods, MenuDetails contains links to the
Foods table. This allows me to save database space by having MenuDetails
only contain the foreign key rather than duplicating each food every time it
is used in a menu. MenuDetails creates a many-to-many relationship between
the Foods and Menus tables.

Foods should be a standalone table that is referenced by the other tables.
Assuming that MenuDetails is an entity like, say, "stroganoff" or
"lunch#1", that would be made up of many foods, you could do this:

No, each menu detail references a single food.

I don't know if that is more clear.

Thanks.

---------------------------

I'm trying to decide the best table structure for a database and was
hoping some folks with more experience might have some suggestions.

I have the following tables: [Users], [Menus], [MenuDetails], and
[Foods]. These tables define menu plans for each user.

The relationships are as follows:

[Foods]<--[MenuDetails]-->[Menus]-->[Users]

(There can be any number of menus per user, any number of menu details
per menu, and any number of menu details referencing the same food.)

This seems straight forward but the problem is that I now need to allow
Users to create their own foods, which can be used in their menus. I hate
to make the data structure more complex.

One thought is to store user foods in the main foods table. I could add a
UserID field to the foods table and keep it NULL for foods that are not
specific to a particular user.

I'm not sure how clear that is. I'm trying to figure out how much sense
this approach makes, particularly using NULL for UserIDs when a food is
not associated with a user. Also, it would be nice to keep the main food
table separate in case some bad code deletes all the foods during
testing. But adding another table for user foods just seems like it would
make the query a nightmare.

It'd be great if anyone could speak to this!

 

[RobinS]

So basically menuDetails is the cross-reference between foods and menus. In
that case, I'd go with this. I would probably call it FoodMenuCrossRef or
something like that, but that's just my personal preference.

Foods
foodID
description

Menus
menuID
description

Users
userID
userName

MenuDetails
menuID
foodID

UserMenus
userID
menuID

UserFoods
userID
foodID

You might not actually need userFoods. You said they "can be" used in their
menus, not that they have to be, though. In that case, you do need it.

If you wanted all of the foods for a user, you would have to take userID and
join UserMenus with MenuDetails and add in any entries from UserFoods that
are not there.

Alternately, you could write a trigger that every time you add a menu to a
user, it takes all the foods and adds them to the UserFoods table. Then
every time they change a menu, you would have to cascade those changes too.
That would be kind of a pain.

Does that help at all?

RobinS.
--------------------------------

Robin,

Can you give some examples of the data? I'm not sure the diff between
MenuDetails and Foods, but I'll take a crack at it.

Rather than contain the actual foods, MenuDetails contains links to the
Foods table. This allows me to save database space by having MenuDetails
only contain the foreign key rather than duplicating each food every time
it is used in a menu. MenuDetails creates a many-to-many relationship
between the Foods and Menus tables.

Foods should be a standalone table that is referenced by the other
tables. Assuming that MenuDetails is an entity like, say, "stroganoff" or
"lunch#1", that would be made up of many foods, you could do this:

No, each menu detail references a single food.

I don't know if that is more clear.

Thanks.

---------------------------

I'm trying to decide the best table structure for a database and was
hoping some folks with more experience might have some suggestions.

I have the following tables: [Users], [Menus], [MenuDetails], and
[Foods]. These tables define menu plans for each user.

The relationships are as follows:

[Foods]<--[MenuDetails]-->[Menus]-->[Users]

(There can be any number of menus per user, any number of menu details
per menu, and any number of menu details referencing the same food.)

This seems straight forward but the problem is that I now need to allow
Users to create their own foods, which can be used in their menus. I
hate to make the data structure more complex.

One thought is to store user foods in the main foods table. I could add
a UserID field to the foods table and keep it NULL for foods that are
not specific to a particular user.

I'm not sure how clear that is. I'm trying to figure out how much sense
this approach makes, particularly using NULL for UserIDs when a food is
not associated with a user. Also, it would be nice to keep the main food
table separate in case some bad code deletes all the foods during
testing. But adding another table for user foods just seems like it
would make the query a nightmare.

It'd be great if anyone could speak to this!

 

[Jonathan Wood]

Thanks. I'm not sure what I think of that approach. In your post, Foods and
UserFoods would basically be the exact same table except one references a
user. That approach just doesn't sit well. I'm leaning towards just keeping
things simple.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

So basically menuDetails is the cross-reference between foods and menus.
In that case, I'd go with this. I would probably call it FoodMenuCrossRef
or something like that, but that's just my personal preference.

Foods
foodID
description

Menus
menuID
description

Users
userID
userName

MenuDetails
menuID
foodID

UserMenus
userID
menuID

UserFoods
userID
foodID

You might not actually need userFoods. You said they "can be" used in
their menus, not that they have to be, though. In that case, you do need
it.

If you wanted all of the foods for a user, you would have to take userID
and join UserMenus with MenuDetails and add in any entries from UserFoods
that are not there.

Alternately, you could write a trigger that every time you add a menu to a
user, it takes all the foods and adds them to the UserFoods table. Then
every time they change a menu, you would have to cascade those changes
too. That would be kind of a pain.

Does that help at all?

RobinS.
--------------------------------

Robin,

Can you give some examples of the data? I'm not sure the diff between
MenuDetails and Foods, but I'll take a crack at it.

Rather than contain the actual foods, MenuDetails contains links to the
Foods table. This allows me to save database space by having MenuDetails
only contain the foreign key rather than duplicating each food every time
it is used in a menu. MenuDetails creates a many-to-many relationship
between the Foods and Menus tables.

Foods should be a standalone table that is referenced by the other
tables. Assuming that MenuDetails is an entity like, say, "stroganoff"
or "lunch#1", that would be made up of many foods, you could do this:

No, each menu detail references a single food.

I don't know if that is more clear.

Thanks.

---------------------------
I'm trying to decide the best table structure for a database and was
hoping some folks with more experience might have some suggestions.

I have the following tables: [Users], [Menus], [MenuDetails], and
[Foods]. These tables define menu plans for each user.

The relationships are as follows:

[Foods]<--[MenuDetails]-->[Menus]-->[Users]

(There can be any number of menus per user, any number of menu details
per menu, and any number of menu details referencing the same food.)

This seems straight forward but the problem is that I now need to allow
Users to create their own foods, which can be used in their menus. I
hate to make the data structure more complex.

One thought is to store user foods in the main foods table. I could add
a UserID field to the foods table and keep it NULL for foods that are
not specific to a particular user.

I'm not sure how clear that is. I'm trying to figure out how much sense
this approach makes, particularly using NULL for UserIDs when a food is
not associated with a user. Also, it would be nice to keep the main
food table separate in case some bad code deletes all the foods during
testing. But adding another table for user foods just seems like it
would make the query a nightmare.

It'd be great if anyone could speak to this!

 

[RobinS]

Simple is always the best. Especially when you come back to it in 3 months,
or 6 months. Then after saying, "I can't remember this, but I have to change
it.", you'll either say, "Damn. Why did I do it THAT way?" or you'll say
"Makes perfect sense. Change done."



RobinS.
------------------------------------

Thanks. I'm not sure what I think of that approach. In your post, Foods
and UserFoods would basically be the exact same table except one
references a user. That approach just doesn't sit well. I'm leaning
towards just keeping things simple.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

So basically menuDetails is the cross-reference between foods and menus.
In that case, I'd go with this. I would probably call it FoodMenuCrossRef
or something like that, but that's just my personal preference.

Foods
foodID
description

Menus
menuID
description

Users
userID
userName

MenuDetails
menuID
foodID

UserMenus
userID
menuID

UserFoods
userID
foodID

You might not actually need userFoods. You said they "can be" used in
their menus, not that they have to be, though. In that case, you do need
it.

If you wanted all of the foods for a user, you would have to take userID
and join UserMenus with MenuDetails and add in any entries from UserFoods
that are not there.

Alternately, you could write a trigger that every time you add a menu to
a user, it takes all the foods and adds them to the UserFoods table. Then
every time they change a menu, you would have to cascade those changes
too. That would be kind of a pain.

Does that help at all?

RobinS.
--------------------------------

Robin,

Can you give some examples of the data? I'm not sure the diff between
MenuDetails and Foods, but I'll take a crack at it.

Rather than contain the actual foods, MenuDetails contains links to the
Foods table. This allows me to save database space by having MenuDetails
only contain the foreign key rather than duplicating each food every
time it is used in a menu. MenuDetails creates a many-to-many
relationship between the Foods and Menus tables.

Foods should be a standalone table that is referenced by the other
tables. Assuming that MenuDetails is an entity like, say, "stroganoff"
or "lunch#1", that would be made up of many foods, you could do this:

No, each menu detail references a single food.

I don't know if that is more clear.

Thanks.

---------------------------
I'm trying to decide the best table structure for a database and was
hoping some folks with more experience might have some suggestions.

I have the following tables: [Users], [Menus], [MenuDetails], and
[Foods]. These tables define menu plans for each user.

The relationships are as follows:

[Foods]<--[MenuDetails]-->[Menus]-->[Users]

(There can be any number of menus per user, any number of menu details
per menu, and any number of menu details referencing the same food.)

This seems straight forward but the problem is that I now need to
allow Users to create their own foods, which can be used in their
menus. I hate to make the data structure more complex.

One thought is to store user foods in the main foods table. I could
add a UserID field to the foods table and keep it NULL for foods that
are not specific to a particular user.

I'm not sure how clear that is. I'm trying to figure out how much
sense this approach makes, particularly using NULL for UserIDs when a
food is not associated with a user. Also, it would be nice to keep the
main food table separate in case some bad code deletes all the foods
during testing. But adding another table for user foods just seems
like it would make the query a nightmare.

It'd be great if anyone could speak to this!

 

[Jonathan Wood]

Yup! <g>

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Simple is always the best. Especially when you come back to it in 3
months, or 6 months. Then after saying, "I can't remember this, but I have
to change it.", you'll either say, "Damn. Why did I do it THAT way?" or
you'll say "Makes perfect sense. Change done."



RobinS.
------------------------------------

Thanks. I'm not sure what I think of that approach. In your post, Foods
and UserFoods would basically be the exact same table except one
references a user. That approach just doesn't sit well. I'm leaning
towards just keeping things simple.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

So basically menuDetails is the cross-reference between foods and menus.
In that case, I'd go with this. I would probably call it
FoodMenuCrossRef or something like that, but that's just my personal
preference.

Foods
foodID
description

Menus
menuID
description

Users
userID
userName

MenuDetails
menuID
foodID

UserMenus
userID
menuID

UserFoods
userID
foodID

You might not actually need userFoods. You said they "can be" used in
their menus, not that they have to be, though. In that case, you do need
it.

If you wanted all of the foods for a user, you would have to take userID
and join UserMenus with MenuDetails and add in any entries from
UserFoods that are not there.

Alternately, you could write a trigger that every time you add a menu to
a user, it takes all the foods and adds them to the UserFoods table.
Then every time they change a menu, you would have to cascade those
changes too. That would be kind of a pain.

Does that help at all?

RobinS.
--------------------------------
Robin,

Can you give some examples of the data? I'm not sure the diff between
MenuDetails and Foods, but I'll take a crack at it.

Rather than contain the actual foods, MenuDetails contains links to the
Foods table. This allows me to save database space by having
MenuDetails only contain the foreign key rather than duplicating each
food every time it is used in a menu. MenuDetails creates a
many-to-many relationship between the Foods and Menus tables.

Foods should be a standalone table that is referenced by the other
tables. Assuming that MenuDetails is an entity like, say, "stroganoff"
or "lunch#1", that would be made up of many foods, you could do this:

No, each menu detail references a single food.

I don't know if that is more clear.

Thanks.

---------------------------
I'm trying to decide the best table structure for a database and was
hoping some folks with more experience might have some suggestions.

I have the following tables: [Users], [Menus], [MenuDetails], and
[Foods]. These tables define menu plans for each user.

The relationships are as follows:

[Foods]<--[MenuDetails]-->[Menus]-->[Users]

(There can be any number of menus per user, any number of menu
details per menu, and any number of menu details referencing the same
food.)

This seems straight forward but the problem is that I now need to
allow Users to create their own foods, which can be used in their
menus. I hate to make the data structure more complex.

One thought is to store user foods in the main foods table. I could
add a UserID field to the foods table and keep it NULL for foods that
are not specific to a particular user.

I'm not sure how clear that is. I'm trying to figure out how much
sense this approach makes, particularly using NULL for UserIDs when a
food is not associated with a user. Also, it would be nice to keep
the main food table separate in case some bad code deletes all the
foods during testing. But adding another table for user foods just
seems like it would make the query a nightmare.

It'd be great if anyone could speak to this!