数据库设计:被其他实体引用的“代码"表 [英] database design: a 'code' table that get referenced by other entities

问题描述

我正在构建一个数据库作为一个简单的练习，它可以托管在任何数据库服务器上，所以我尽量保持标准.基本上我想做的是一个被其他实体引用的代码"表.我解释一下:

I am building a database as a simple exercise, it could be hosted on any database server, so I am trying to keep things as much standard as possible. Basically what I would like to do is a 'code' table that get referenced by other entities. I explain:

xcode
id code
r  role
p property

code
r admin
r staff
p title
....

那么我想有一些像这样的观点:

then I would like to have some view like:

role (select * from code where xcode='r')
r admin
r staff

property (select * from code where xcode='p')
p title

那么，假设我们有一个实体

then, suppose we have an entity

myentity
id - 1
role - admin (foreign key to role)
title - title (foreign key to property)

显然我无法为视图创建外键，但这是为了说明我的想法.我如何尽可能使用标准 sql 语法来反映这种行为，然后作为第二种选择，使用数据库附加功能，如 trigger ecc...?

Obviously I cannot create foreign key to a view, but this is to tell the idea I have in mind. How can I reflect such behaviour using whenever possible, standard sql syntax, then as a second option, database additional features like trigger ecc... ?

因为如果我告诉 myentity 中的角色和标题是代码"的外键，而不是视图，没有什么能阻止我在标题字段中插入角色.

Because if I tell that role and title in myentity are foreign key to 'code', instead of the views, nothing would stop me to insert a role in title field.

推荐答案

我曾在系统上工作过，所有代码都有一个表，而其他系统每个代码只有一个表.我绝对更喜欢后一种方法.

I have worked on systems with a single table for all codes and others with one table per code. I definitely prefer the latter approach.

每个代码一个表的优点是:

The advantages of a table per code are:

1. 外键.正如您已经发现的那样，不可能通过单个表的外键强制遵守允许的值.使用检查约束是一种替代方法，但维护成本较高.
2. 性能.代码查找通常不是性能瓶颈，但如果优化器知道它正在从四行而不是四百行的表中检索记录，它无疑有助于优化器对执行路径做出明智的决定.
3. 代码组.有时我们希望将代码组织成子部分，通常是为了更容易呈现复杂的值列表.如果我们每个代码都有一个表格，我们在结构方面会更加灵活.

此外，我注意到您希望能够在任何数据库服务器上"进行部署.在这种情况下，避免触发.在大多数情况下，触发器通常是坏消息，但它们具有特定于产品的语法.

In addition I notice that you want to be able to deploy "on any database server". In that case avoid triggers. Triggers are usually bad news in most scenarios, but they have product-specific syntax.

这篇关于数据库设计:被其他实体引用的“代码"表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！