Suppose an automatically generated ID is used as follows as the device table's primary key instead of the natural combination of model and serial number.
假设使用自动生成的ID作为设备表的主键,而不是模型和序列号的自然组合。
id model sn detail
---------------------------
1 A 01 blah
2 A 02
3 B 01
Now in order to relate another table to device, how do you design a form for data entry so that the user can select a device in a natural way? That is to say, choose the model in a combo box, then choose the SN in the next combo box, which shows only the serial numbers from the chosen model as options.
现在,为了将另一个表与设备相关联,如何设计数据输入表单,以便用户可以自然地选择设备?也就是说,在组合框中选择模型,然后在下一个组合框中选择SN,该组合框仅显示所选模型中的序列号作为选项。
If the first combobox is unbound, then the model won't show when you return to the form for review. If the the first combobox is bound to model (or more realistically model_id) and the second to id (while showing sn in the combobox for the user to select), then the first is completely superfluous and thus probably violates a normal form. If the first is bound to model and the second to sn, then we're just using composite natural keys and might as well get rid of the surrogate key altogether.
如果第一个组合框未绑定,则返回表单进行审核时,模型将不会显示。如果第一个组合框绑定到模型(或更逼真的model_id),第二个组合绑定到id(同时在组合框中显示sn供用户选择),则第一个组合框完全是多余的,因此可能违反了正常形式。如果第一个绑定到模型而第二个绑定到sn,那么我们只是使用复合自然键,并且可能完全摆脱代理键。
I've done this before using natural keys and it worked well. But since surrogate keys seem to be favored, I wonder how it would be done given that design choice.
我在使用自然键之前已经完成了这项工作并且运行良好。但由于代理键似乎受到青睐,我想知道如果设计选择如何才能完成。
1 个解决方案
#1
2
From the user standpoint, having the first bound to model and the second bound to sn is the most intuitive and realistically the only viable option. Say I have the device with model: B; sn: 01. I am going to have no way of knowing that my device is also id: 3 unless there is some sticker designating it as id: 3 or I was notified that 3 was the devices number. But the model and sn are always going to be placed on the device by the manufacturer.
从用户的角度来看,第一个绑定到模型,第二个绑定到sn是最直观和现实的唯一可行选项。假设我的设备有型号:B; sn:01。我将无法知道我的设备也是id:3,除非有一些贴纸将其指定为id:3或者我被告知3是设备编号。但是模型和sn总是由制造商放置在设备上。
From the database standpoint, either key works. Both the keys (id) and (model, sn) uniquely identify the device. Having id is superfluous, true, but it can be used in related tables as a table constraint to reduce total DB size (serial numbers can be large and will need to be a variant of CHAR) and simplify joined queries.
从数据库的角度来看,任一关键都有效。密钥(id)和(model,sn)都唯一地标识设备。 id是多余的,是真的,但它可以在相关表中用作表约束以减少总DB大小(序列号可以很大并且需要是CHAR的变体)并简化连接查询。
IMO, have both. Use the key (model, sn) whenever a user is involved and use (id) for joining all related tables together.
IMO,两者都有。每当涉及用户时使用密钥(model,sn)并使用(id)将所有相关表连接在一起。
#1
2
From the user standpoint, having the first bound to model and the second bound to sn is the most intuitive and realistically the only viable option. Say I have the device with model: B; sn: 01. I am going to have no way of knowing that my device is also id: 3 unless there is some sticker designating it as id: 3 or I was notified that 3 was the devices number. But the model and sn are always going to be placed on the device by the manufacturer.
从用户的角度来看,第一个绑定到模型,第二个绑定到sn是最直观和现实的唯一可行选项。假设我的设备有型号:B; sn:01。我将无法知道我的设备也是id:3,除非有一些贴纸将其指定为id:3或者我被告知3是设备编号。但是模型和sn总是由制造商放置在设备上。
From the database standpoint, either key works. Both the keys (id) and (model, sn) uniquely identify the device. Having id is superfluous, true, but it can be used in related tables as a table constraint to reduce total DB size (serial numbers can be large and will need to be a variant of CHAR) and simplify joined queries.
从数据库的角度来看,任一关键都有效。密钥(id)和(model,sn)都唯一地标识设备。 id是多余的,是真的,但它可以在相关表中用作表约束以减少总DB大小(序列号可以很大并且需要是CHAR的变体)并简化连接查询。
IMO, have both. Use the key (model, sn) whenever a user is involved and use (id) for joining all related tables together.
IMO,两者都有。每当涉及用户时使用密钥(model,sn)并使用(id)将所有相关表连接在一起。