数据库设计问题 [英] database design question

问题描述

我建立一个数据库作为一个简单的练习，它可以托管在任何数据库服务器，所以我尽量保持尽可能多的标准。基本上我想做的是一个代码表，由其他实体引用。我解释：

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语法反映这种行为，然后作为第二个选项，数据库附加功能，如触发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... ?

因为如果我告诉角色和头衔在虚名是外键的代码，而不是意见，没有什么会阻止我插入一个角色在标题字段。

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.

感谢
Leonardo

thanks Leonardo

推荐答案

我已经为所有代码使用单个表进行系统工作，其他每个代码使用一个表。我肯定喜欢后一种方法。

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屋！