对于相同的列相同的表数据建模 [英] Data modeling for Same tables with same columns

问题描述

我也有相同的列数和名称，因为他们都是查找表许多表。
对于例如，有LabelType和任务类型的表。 LabelType和任务类型表有TYPEID和类型名列。它们将被用作其他表，如与shippingLog表和任务类型表EmployeeTask表LabelType表的外键。

I have many tables that have same number of columns and names because they are all lookup tables. For example, there are LabelType and TaskType tables. LabelType and TaskType tables have TypeID and TypeName columns. They will be used as a foreign key in other tables such as LabelType table with shippingLog table and TaskType table with EmployeeTask Table.

LabelType Table
TypeID TypeName
1      Fedex
2      UPS
3      USPS

TaskType Table
TypeID TypeName
1      Receiving
2      Pickup
3      Shipping

到目前为止，我已经20多桌，我期待它即将被不断增加。
我有没有问题，但我只是想知道是否有使用表或没有任何更好或更聪明的方法。我甚至想将所有这些表作为一个查找表型，并通过添加查找表的外键区分。查找表可以有像标签，任务数据，等等。然后我只需要为所有那些查找数据的一个或两个表。

So far, I have more than 20 tables and I am expecting it is going to be keep increasing. I have no problem with it , but I am just wondering whether there is any better or smarter way of using tables or not. I was even thinking to consolidate all those tables as one lookup Type Table and differentiate them by adding a foreign key from lookup table. The lookup table may have data like Label, Task, and etc. Then I just need one or two tables for all those lookup data.

请，劝我，如果你有更好或者数据建模的更聪明的方式。

Please, advise me if you have any better or smarter way of data modeling.

推荐答案

仅仅是因为数据具有相似的结构，并不意味着它具有相同的意义或同样的限制。让您的查找表分开。这使得外键分开的，因此数据库可以保护自己免受引用错误类型查找数据。 ¹

Just because data has similar structure doesn't mean it has the same meaning or same constraints. Keep your lookup tables separate. This keeps foreign keys separate, so the database can protect itself from referencing the wrong kind of lookup data.¹

我希望关系数据库管理系统所支持的产业，在那里你可以在父表中定义的基本结构和只添加特定FKS的子表。目前的情况是，你需要忍受一些重复你的DDL ...

I wish relational DBMSes supported inheritance, where you could define the basic structure in the parent table and just add specific FKs in the child tables. As it stands now, you'll need to endure some repetition in your DDL...

注：从养查找表分开规则可能是，当一个例外您系统需要是动态的（即能够添加新类型的查找数据，而无需实际在数据库中创建新的物理表），但它并不从你的问题的方式。

NOTE: One exception from "keep lookup tables separate" rule might be when your system needs to be dynamic (i.e. be able to add new kinds of lookup data without actually creating new physical tables in the database), but it doesn't look that way from your question.

的 ¹ 一个大的查找表，FKS本身不会停止（例如） ShippingLog从引用意味着在 EmployeeTask 表中的行表。使用确定的关系和迁移的PK，你可以保护自己免受这一点，但并非没有引入一些冗余，并需要一些细致的约束。它的清洁，可能更高性能的简单地做正确的事，并保持查找表中分离出来。的

¹ With one big lookup table, FKs alone won't stop (for example) the ShippingLog table from referencing a row meant for the EmployeeTask table. By using identifying relationships and migrating PKs, you can protect yourself from this, but not without introducing some redundancies and needing some careful constraining. It's cleaner and probably more performant to simply do the right thing and keep lookup tables separate.

这篇关于对于相同的列相同的表数据建模的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！