对此桌子设计的推荐/建议......

发布于 2024-11-04 11:21:20 字数 507 浏览 0 评论 0原文

我这里有三张桌子...... 一是菜单，二是饮料和食物。

一份菜单数据只能有一种饮料或一种食物。

所以，菜单表有这样的东西...... [id][type][type_fk]

如果菜单表类型为“D”，它将查找饮料表，并使用 type_fk 来查找饮料... ...这里有一个更详细的示例：

id:1,type:D, type_fk:1
id:2,type:F, type_fk:1

在饮料表，我们

id:1, name:coke, unit:ml

在食物表中有，

id:1, name:chicken

结果我想分开两个表是因为饮料表有“单位”列，但食物表没有这个......所以，我将根据类型和type_fk从用户那里获取信息。所以，在第一个例子中，我可以获得“可乐”，第二个例子中，我可以获得“鸡肉”。

请留下您对此设计的评论。

原文

I have three table here....
One is menu, and second is drink, and food.

One menu data can only have either one drink or one food.

So, the menu table have something like this....
[id][type][type_fk]

if the menu table type is "D", it will find the drink table, and use the type_fk, to find the drink... ...Here is a more detail example:

id:1,type:D, type_fk:1
id:2,type:F, type_fk:1

in the drink table, we have

id:1, name:coke, unit:ml

in the food table, we have

id:1, name:chicken

The result why I want to separate two tables is because the drink table have "unit" column, but the food table don't have this... ...So, I will achieve information from user based on the type and the type_fk. So, in the first example, I can get the "coke", and the second one, I can get the "chicken".

Please drop your comments on this design.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蹲墙角沉默 2024-11-11 11:21:20

我建议您使用一个表而不是两个表：

Menu (Id, ItemId)
Item (Id, Name, Type, Unit)

其背后的原因是为了强制引用完整性。 Menu.ItemId 将是引用 Item.Id 的外键。如果您有 2 个表，您将无法强制执行引用完整性。

我相信你不应该担心稀疏的列。例如，将鸡肉单位存储为一块或将其留空。

编辑：
如果您想避免使用稀疏表（即某些列未被使用的表），那么您可能会考虑类似以下内容：

Menu (Id, ItemId)
Item (Id, Name)
ItemProperties (ItemId, Name, Value)

例如，

Item:
-----
Id    Name
1     Chicken
2     Coke

ItemProperties:
---------------
ItemId    Name    Value
1         Price   10
1         Type    Chinese
2         Price   1
2         Unit    ml
2         Size    300

这样您就可以保持引用完整性并避免稀疏表。

如果您使用的是 SQL Server 2008，请检查此 SQL Server 2008 稀疏列

I would suggest that you have a single table instead of 2 tables:

Menu (Id, ItemId)
Item (Id, Name, Type, Unit)

The reason behind that is to enforce referential integrity. Menu.ItemId will be a foreign key that references Item.Id. If you have 2 tables you won't be able to enforce referential integrity.

I believe you shouldn't worry about sparse columns. For example store the Unit of chicken as piece or leave it empty.

Edit:
If you want to avoid having a sparse table, a table with some columns not being used, then you might consider something like:

Menu (Id, ItemId)
Item (Id, Name)
ItemProperties (ItemId, Name, Value)

For example

Item:
-----
Id    Name
1     Chicken
2     Coke

ItemProperties:
---------------
ItemId    Name    Value
1         Price   10
1         Type    Chinese
2         Price   1
2         Unit    ml
2         Size    300

This way you are keeping referential integrity and avoiding sparse tables.

If you are using SQL Server 2008, check this SQL Server 2008 Sparse Columns

回复收藏 0 原文