不同Schema设计的优缺点都与一个数据项有关系 [英] Pros and Cons of different Schema designs for tables that all have a relationship to one data item

问题描述

我正在开发一个具有数据库后端的系统。我打算每个表都有一个PK是任意的，系统生成和维护的。

我的环境（用于开发和生产）是Windows 7;德尔福和一个嵌入式数据库（可能是Firebird）。

我的数据结构包括一个表OWNER，与它相关联的信息很少 - 可能不仅仅是名称和描述。数据结构的其余部分将是大约50个表，每个表的名称和描述，以及其他属性的列表 - 平均每个表20个。此外，可以有20个关联表，其中大多数只具有FK属性，尽管一小部分将具有附加属性。这是我的意图（至少开始）使模式完全正常化。

对于给定的OWNER，大多数表将具有O（10 ^ 3至10 ^ 4）记录，尽管一个或两个将具有O（10 ^ 5至10 ^ 6）。 OWNER的数量可能是O（10 ^ 2到10 ^ 3）。大多数访问可能是集群的 - 一个OWNER将存在大量访问权限，然后对另一个OWNER进行大量访问。

每个数据项将属于一个所有者，不能从OWNER转移到OWNER。对数据库的所有访问都将知道他们正在使用的OWNER;无法访问将不得不访问多个OWNER的内容。

在设计我的架构时，我知道以下三个选项：

1. 按照说明使用OWNER表。在每个表中使用OWNER PK作为外键（尽管可能不是关联表）。将OWNER PK作为附加条款添加到每个查询，连接，存储过程，视图等中。


2. 向包含小代码的OWNER表中添加一列，例如四位整数。对于每个OWNER，创建一组重复的表 - 特定OWNER的表名称将添加适当的代码作为后缀。这将要求每次访问先前从数据库获取后缀代码。那么访问将是相关的一组表。


3. 对于每个OWNER，创建一个重复的数据库，其中的表和表名被复制。这意味着可能会有一个附属公共数据库，其中包含有关相关重复数据库的数据。再次，这个常见的数据库需要在任何访问之前进行访问，或者对一个特定的OWNER进行一系列访问。

这些不同的方法？我是否错过了整体设计的其他选择？

编辑反转 - 我提供了自己的答案。

解决方案

选项＃1是迄今为止最好的。很明显，你打算使用一个系统生成的id号码来记录这个记录，否则你会遇到一个问题，如果你的OWNER pk是个人的名字，最后你会得到多个John Smith。

选项＃2可以正常工作，但增加了太多的复杂性。只要你已经将OwnerId作为OWNER表中的PK，jsut fk就可以休息，并节省额外的开销和努力。

选项＃3。这个选项是非常好的，如果你有一个差的前端界面，并希望能够轻松地读取记录手动，并了解他们...但所有额外的表将减慢你的速度，并创建额外的子例程，写入前端以跟踪表。

I am developing a system that will have a database backend. I am intending that every table have a PK that is arbitary, system generated and maintained.

My environment (for development and production) is Windows 7; Delphi; and an embedded database (probably Firebird).

My data structures include one table OWNER, that has very little information associated with it - probably no more than name and description. The rest of the data structure will be about 50 tables, each with name and description, together with a list of other attributes - say 20 per table on average. In addition there could be 20 associative tables most of which will have only FK attributes, though a small proportion will have additional attributes. It is my intention (at least to start with) to have the schema fully normalised.

For a given OWNER, most table will have O(10^3 to 10^4) records, though one or two will have O(10^5 to 10^6). The number of OWNERs is likely to be O(10^2 to 10^3). Most accesses are likely to be clustered - there will be a substantial number of accessess for one OWNER, and then a substantial number of accesses for another OWNER.

Every data item will belong to exactly one OWNER, and cannot be transferred from OWNER to OWNER. All accesses to the database will know what OWNER they are using; no access will ever have to access the contents of more than one OWNER.

I am aware of the following three options in designing my schema:

1. Use the OWNER table as described. Use the OWNER PK as a foreign key in every table (though possibly not the associative tables). Add the OWNER PK as an additional clause into every query, join, stored procedure, view etc.
2. Add a column to the OWNER table containing a small code - say a four digit integer. For each OWNER, create a duplicate set of tables - the table names for a particular OWNER will have the appropriate code added as a suffix. This would require every access to have previously obtained the suffix code from the database. Then the access will be of the relevant set of tables.
3. For each OWNER, create a duplicated database, with the tables and table names being duplicated. This implies that there would probably be a subsidiary common database containing data about the relevant duplicate database. Again this common database would need accessing before any access - or series of accesses for one particular OWNER.

What are the pros and cons of these different approaches? Have I missed any other options for overall design?

Edit reversed - I provided my own answer instead.

解决方案

Option #1 is by far the best. Obviously you are planning to use a system generated id number for this record as well...otherwise you will run into a problem if your OWNER pk is the person's name and you end up with multiple John Smith.

Option #2 would work fine, but adds too much complexity. So long as you already have the OwnerId as the PK in OWNER table, jsut fk that for the rest and save the additional overhead and effort.

Option #3. This option is great if you have a poor front-end interface and want to be able to easily read through the records manually and make sense of them...but all the extra tables will slow you down and create extra sub-routines that have to be written into the front-end to keep track of the tables.

这篇关于不同Schema设计的优缺点都与一个数据项有关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！