数据库设计：如何实现“其他”数据库选项？
我正在尝试设计一个应用程序，它将使用数据库来存储有关盒子的一些信息。
这些框位于特定的预定义站点，例如“车库”、“客厅”等。可以使用下拉框轻松选择位置。但有时它们可能在其他地方，比如“乔的”，这几乎是特定的，而且大多是不可重复的。
所以我的问题是如何将其存储在数据库中？ 建立一个简单的关系
BOXES
| ID_BOX | ID_LOCATION |
| 1 | 1 |
| 2 | 3 |
我的第一个想法是与一张带有盒子的桌子和另一张带有位置的桌子
LOCATION
| ID_LOCATION | LOCATION |
| 1 | Garage |
| 2 | Living Room |
但是我怎样才能把额外的选项放在这里呢？将它们“临时”添加到位置表中？或者创建一个“其他”字段并将此信息存储在其他地方？
I'm trying to design an application that will use a DB to store some information about boxes.
These boxes are located at a specific, predefined site such as "Garage", "Living Room", etc. Locations that could easily be chosen with a dropdown box . But sometimes they can be somewhere else like "Joe's" that would pretty much be specific and mostly non repeatable.
So my question is how to store this in the DB?
My first idea was to make a simple relation with a table with boxes
BOXES
| ID_BOX | ID_LOCATION |
| 1 | 1 |
| 2 | 3 |
And another table with the locations
LOCATION
| ID_LOCATION | LOCATION |
| 1 | Garage |
| 2 | Living Room |
But how can I put the extra options in here? Add them "temporarily" to the Locations Table? Or make an "Other" field and store this information somewhere else?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我只想采用您当前的方案，其中
BOXES中的位置代码引用LOCATION中的位置，但其中一个位置是“OTHER”。然后，我会在
BOXES中调用OTHER-LOCATION中的另一列，仅当位置设置为OTHER时，您的应用程序才会使用该列。有人可能会说这违反了 3NF，但我不这么认为。
OTHER_LOCATION的值仍然取决于密钥，整个密钥，除了密钥之外什么都没有（所以请帮助我，Codd）。从这个意义上说，它就像您必须填写的那些无休止的表格一样，它们为您提供了一份常见选择列表，其中一个是“其他”，以及一个只有在您选择该选项时才需要填写的框。
I would simply have your current scheme where a location code in
BOXESreferences a location inLOCATION, but one of those locations is "OTHER".Then I would have another column in
BOXEScallOTHER-LOCATIONwhich is used by your application only if the location is set toOTHER.It could be argued that this violates 3NF but I don't think so. The value of
OTHER_LOCATIONis still dependent on the key, the whole key and nothing but the key (so help me, Codd).In this sense, it's exactly like those interminable forms you have to fill out where they give you a list of common choices with one of them being "Other", along with a box to fill out only if you choose that option.
如果这些位置肯定是唯一的，并且一旦对象被移动或删除，您就不需要持久化，您可以在删除时添加一个 SQL 函数来检查某个位置是否有任何对象，如果没有：delete的位置。
如果这听起来很危险或者不是一个好主意（在大多数情况下这将是一个坏主意），您可以向 BOXES 添加一列来指定该位置是在常规位置还是在临时位置，为每个位置创建一个表并进行第四个表中的关系。这样，您只能将 REGULAR_LOCATIONS 中的寄存器添加到下拉菜单中，并添加“其他”选项以写入 TEMP_LOCATIONS
或者更好，您可以向位置表中添加一列，并指定它们是否是常规的，如果它们是常规的你不会在下拉菜单中显示它们。并且您可以保留当前的计划。
If these locations are sure to be pretty much unique and you do not need persistence once the object has been moved or deleted you could add an SQL function when deleting to check if there are any objects at a certain location, if there are non: delete the location.
If that sound dangerous or not a good idea (for most cases it will be a bad idea) you could add a column to BOXES to specify if the location is in a regular place or in a temporary place, create a table for each and make the relation in a 4rd table. This way you could only add the registers in REGULAR_LOCATIONS to your drop menu and add the 'others' option to write into TEMP_LOCATIONS
Or even better, you could add a column to the locations table and specify if they are regular or not, if they aren't you won't show them in the drop down menu. And you get to keep you r current scheme.
为了简单起见，是的，向 BOXES 表添加一个名为 OTHER_LOCATION 或类似名称的 varchar。然后，您可以允许 ID_LOCATION 为 NULL，也可以输入其他位置的特定位置行，并让您的应用程序代码将 ID_LOCATION 设置为该值，并在使用时要求在 OTHER_LOCATION 列中输入内容。您对解决方案的感受取决于您对是否要使用空值以及如何编写联接的感受。我可能会建议使用“其他”行，也许是 ID_LOCATION '1'。那么您就不必担心两个表之间的内部联接。
Just keeping it simple, yes add a varchar to the BOXES table called OTHER_LOCATION or something similar. Then you can either allow ID_LOCATION to be NULL, or you can enter a specific Location row for other, and have your application code set the ID_LOCATION to that value, and require input into the OTHER_LOCATION column when it is used. How you feel about the solution, depends on your feelings about whether or not you want to use nulls, and how you want to write your joins. I'd probably recommend having the "other" row, perhaps as ID_LOCATION '1'. Then you don't have to be concerned about inner joins between the two tables.
如果位置是公共的还是私有的，我会向 location 选项卡添加额外的字段，其中包含信息（您也可以插入用户 ID 或 NULL 表示公共位置）。然后您可以将新值插入位置表中。
另一种选择是删除外来键并通过 varchar 字段值保持逻辑关系。在用户界面中，您显示“位置”表中的值，并将用户提供的值直接保存到“框”表中。
I would add extra field to the location tabe with information if location is public or private (you can also insert user id or NULL for public). Then you can insert new value into the location table.
Another option is to remove foregin key and keep logical relation via varchar field values. In user interface you show values from the location table and save value provided by user directly into the boxes table.