在MySQL中正确实现超类型子类型 [英] Implementing super-type subtype correctly in MySQL

问题描述

下面是一个数据库的图，其中我试图确定适当的设计。

• 员工/经理与客户相关联。


• partyid 是全球代表一个人的方式;客户，员工，经理。它需要一直传播下去吗？


• 执行结算，报告等其他表格。 strong>，凭证等表需要有自己的各自的id作为主键，例如 billingid ， reportingid ， credentialid 等。

<

以及可能的雇员 。 和 的客户需要报告时间。

/ p>

解决方案

表格party看起来不正确。与来自这个其他SO问题。

在这种结构中，参与者id号码向下传播。它通常应该是存储关于某人的数据的表中的主键或外键。

在表报告中，它看起来像主键应该不是'partyid'。这将允许每个员工只有一行，我不认为你打算。如果我是对的，你可以考虑对{partyid，date}的 NOT NULL UNIQUE 约束，以及 PRIMARY KEY 约束在新列reportid上。表旅行和表演可能会引用reportid。 （但继续阅读。）

您的图表中有一些实体获得附加键的地方：例如，您的公司为其员工分配一个唯一的员工ID号。没有理论上的理由，你不能使用'employid'而不是'partyid'从那点到引用员工。但是是一个实用的原因，你可能不想这样做。它增加了连接的数量。

例如，如果表credential，tool，certification，academic和compliance引用employee.employid而不是employee.partyid ，你不能只是加入合规和派对获得该人的名字。

执行其他表格（例如帐单，报表，凭证等）
需要有自己各自的id作为主键，例如
billid，reportingid，credentialid等？

他们需要一个主键;主键不一定必须是id号。如果有一个现有的自然键，您必须标识它，并声明它唯一的UNIQUE。

表订单应该只有orderid使用外键引用来标识客户。在某些情况下，重命名列很有意义。在客户的情况下，调用其键'customerid'而不是'parytid'可能是有意义的。

 创建域PARTY_ID为整数not null; 
  

然后，在需要一个party id号码的地方，我会使用域。

 创建表客户（
 customerid PARTY_ID主键引用方（partyid），
 ... 
  

我更愿意看到一个管理器表，对它的引用将保证manager.managerid将解析给实际的经理，而不只是给任何员工。

Below is a diagram of a database in which I am trying to determine the appropriate design for. Here are a few notes.

• Employees/managers are associated with customers.
• The partyid is a way to globally represent a person; customer, employee, manager. Does it need to propagate all the way down? Should it be a primary key in all tables or just the tables that represent an individual?
• Do the other tables such as billing, reporting, credential, etc tables need to have their own respective id's that are primary keys, e.g. billingid, reportingid, credentialid, etc?

Some notes about the interaction of the entities.

• employees have a manager(s) associated with them.
• customers have a manager(s) and possibly employees associated with them.
• customers and employees will need to report time billed.

解决方案

The table "party" doesn't look right. Compare to the source code from this other SO question.

In this kind of structure, the party id number does propagate downward, so to speak. It should usually be either a primary key or a foreign key in tables that store data about a person.

In your table "reporting", it looks like the primary key shouldn't be 'partyid'. That would allow only one row per employee, which I don't think you intended. (I could be wrong.) If I'm right about that, you might consider a NOT NULL UNIQUE constraint on {partyid, date}, and a PRIMARY KEY constraint on a new column, 'reportid'. The tables "travel" and "performance" would probably reference 'reportid'. (But keep reading.)

There are places in your diagram where an entity gets an additional key: your company assigns a unique employee id number to its employees, for example. There's no theoretical reason you can't use 'employid' instead of 'partyid' from that point on to reference employees. But there is a practical reason you might not want to do that. It increases the number of joins.

For example, if the tables "credential", "tool", "certification", "academic", and "compliance" referenced employee.employid instead of employee.partyid, you couldn't just join "compliance" and "party" to get the person's name. You'd have to join "employee", too.

Do the other tables such as billing, reporting, credential, etc tables need to have their own respective id's that are primary keys, e.g. billingid, reportingid, credentialid, etc?

They need to have a primary key; the primary key doesn't necessarily have to be an id number. If there's an existing natural key, you have to identify it and declare it UNIQUE anyway.

The table "orders" should probably have only "orderid" as its primary key; use a foreign key reference to identify the customer. In some cases, it makes sense to rename columns. In the case of customers, it might make sense to call its key 'customerid' instead of 'parytid'. I'd create a domain, myself.

create domain PARTY_ID as integer not null;

Then, everywhere that needed a party id number, I'd use the domain instead.

create table customers (
    customerid PARTY_ID primary key references parties (partyid),
...

I'd prefer to see a table of managers, too. A reference to it would guarantee that manager.managerid would resolve to an actual manager, not just to any employee.

这篇关于在MySQL中正确实现超类型子类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！