对整个数据库使用共享列的主表是一种很好的做法吗? [英] Is using a Master Table for shared columns good practice for an entire database?

问题描述

下面，我将解释我正在处理的数据库的基本设计.因为我不是 DB，所以我担心我的轨道是好的还是坏的，所以我想把它放在堆栈上以获得一些建议.我找不到适合我的设计的类似讨论.

Below, I explain a basic design for a database I am working on. As I am not a DB, I am concerned if I am on a good track or a bad one so I wanted to float this on stack for some advice. I was not able to find a similar discussion that fit's my design.

在我的数据库中，每个表都被视为一个实体.实体可以是客户帐户、个人、用户、一组员工信息、承包商信息、卡车、飞机、产品、支持票等.这是我当前的实体(表格)...

In my database, every table is considered an entity. An Entity could be a customer account, a person, a user, a set of employee information, contractor information, a truck, a plane, a product, a support ticket, etc etc. Here are my current entities (Tables)...

• 人
• 用户
• 帐户
• 帐户用户
• 地址
• 员工信息
• 承包商信息

为了存储有关这些实体的信息，我有两个表:

And to store information about these Entities I have two tables:

Entity Tables
-EntityType
-> EntityTypeID (INT)
-Entities
-> EntityID (BIGINT)
-> EnitityType (INT) : foreign key

我制作的每个表都有一个自动生成的主键，以及实体表的 entityID 列上的外键.

Every table I have made has an Auto Generated primary key, and a foreign key on an entityID column to the entities table.

在实体表中，我有一些共享字段，例如，

In the entities table I have some shared fields like,

• 创建日期
• 修改日期
• 用户_创建
• User_Modified
• IsDeleted
• CanUIDelete

我在所有表上使用触发器以在插入时自动创建具有正确实体类型的实体条目.更新触发器更新 LastModified 日期.

I use triggers on all of the table's to automatically create their entity entry with the correct entity type on inserts. And update triggers update the LastModified date.

从应用层的角度来看，所有代码所要做的就是关心单个实体(除了 USER_Modified/User_Created 字段它通过加入 entityID 来更新")

From an application layer point of view, all the code has to do is worry about the individual entities (except for the USER_Modified/User_Created fields "it does updates on that" by joining on the entityID)

现在实体表的原因是我计划拥有 EAV 模型，因此每个实体类型都可以使用自定义字段进行扩展.它还可以用作存储有关实体的元数据(例如创建/修改的字段)的合适位置.

Now the reason for the entities table, is down the line I plan on having an EAV model, so every entity type can be extended with custom fields. It also serves as a decent place to store metadata about the entities (like the created/modified fields).

我刚开始接触数据库设计，想要第二个意见.

I'm just new to DB design, and want a 2nd opinion.

推荐答案

我计划拥有一个 EAV 模型，这样每个实体类型都可以使用自定义字段进行扩展.

I plan on having an EAV model, so every entity type can be extended with custom fields.

为什么?您的所有实体都需要以这种方式进行扩展吗?可能不会——在大多数应用程序中，最多只有一两个实体可以从这种级别的灵活性中受益.其他实体实际上受益于不一直变化的稳定性和清晰度.

Why? Do all your entities require to be extensible in this way? Probably not -- in most applications there are one or two entities at most that would benefit from this level of flexibility. The other entities actually benefit from the stability and clarity of not changing all the time.

EAV 是内部平台效应的一个例子:

EAV is an example of the Inner-Platform Effect:

内部平台效应是将系统设计为可定制的结果，以至于它最终成为其设计平台的糟糕复制品.

The Inner-Platform Effect is a result of designing a system to be so customizable that it ends becoming a poor replica of the platform it was designed with.

换句话说，现在您有责任编写应用程序代码来完成适当的 RDBMS 已经提供的所有事情，例如约束和数据类型.即使像 NOT NULL 这样的强制列这样简单的事情在 EAV 中也不起作用.

In other words, now it's your responsibility to write application code to do all the things that a proper RDBMS already provides, like constraints and data types. Even something as simple as making a column mandatory like NOT NULL doesn't work in EAV.

有时一个项目确实需要很多表.但是，如果您认为只制作两张表就简化了项目，那您就是在自欺欺人.您仍然会拥有与拥有桌子一样多的不同实体，但现在由您来防止它们变成一堆垃圾.

It's true sometimes a project requires a lot of tables. But you're fooling yourself if you think you have simplified the project by making just two tables. You will still have just as many distinct Entities as you would have had tables, but now it's up to you to keep them from turning into a pile of rubbish.

在您为 EAV 投入太多时间之前，请阅读有关一家几乎停止运作的公司的故事，因为有人试图使其数据存储库任意灵活:坏车.

Before you invest too much time into EAV, read this story about a company that nearly ceased to function because someone tried to make their data repository arbitrarily flexible: Bad CaRMa.

我还在一篇博文中写了更多关于 EAV 的内容，EAV FAIL，在我书中的一章中，SQL 反模式:避免数据库编程的陷阱.

I also wrote more about EAV in a blog post, EAV FAIL, and in a chapter of my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

这篇关于对整个数据库使用共享列的主表是一种很好的做法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！