一对多扭曲 [英] One-to-many with a twist
问题描述
建模的最佳方法是：假设我有两个对象：代理商
和Publisher，两者都有一个1-与员工的关系。这是一个真正的1对n关系，因为每个员工只能为一个
代理商或一个发布商工作。让我们进一步假设我不能引入一个持有1对n关系的
超类型（例如雇主）。
我的首选解决方案是在Emplyee中有一个外键，它可以链接到代理商或发行商的主键（我所有的主要商品都是64位ID，这些代码是无效的。数据库）。但是，现在
我无法映射双向关联，而没有在Employee中指示
这是否是代理。或发布者关系
（ala< ANY>）。
我的另一个选择是使用两个表，AgencyEmployee和
PublisherEmployee，然后可以将其作为传统的1对n
双向关联链接。
在这种情况下，您认为最佳做法是什么？
干杯，
Jen
Hi,
What is the best way to model this: Assume I have two objects: Agency
and Publisher, and both have a 1-to-n relationship to Employee. This is
a true 1-to-n relationship, as each Employee can only work for one
Agency or one Publisher. Let''s assume further that I cannot introduce a
supertype (e.g. Employer) which holds the 1-to-n relationship.
My preferrd solution is to have a foreign key in Emplyee that can
either link to a primary key of Agency or Publisher (all my primary
keys are 64-bit IDs that are unqiue across the database). However, now
I won''t be able to map a bi-directional association, without indicating
in Employee whether this is an "Agency" or "Publisher" relationship
(ala <ANY>).
My other option is to use two tables, AgencyEmployee and
PublisherEmployee, which can then be linked as traditional 1-to-n
bidirectional associations.
What do you guys consider best practice in this situation?
Cheers,
Jen
推荐答案
它会可能是一个错误，不创建雇主表。
特别是因为你几乎肯定有一些常见的代理商
和Publisher。否则将该实体拆分为命名表看起来像是一个弱设计的
。我可能会这样做：
CREATE TABLE雇主（employer_id INTEGER PRIMARY KEY，emp_type CHAR（1）
NOT NULL CHECK（emp_type IN（ ''A''，''P''）），UNIQUE（employer_id，emp_type），
emp_name VARCHAR（35）NOT NULL UNIQUE）
CREATE TABLE Agencies（employer_id INTEGER PRIMARY KEY，emp_type CHAR（1）
DEFAULT''A''NOT NULL CHECK（emp_type =''A''）/ *，...其他列特定
到代理机构* /，FOREIGN KEY（employer_id，emp_type）REFERENCES雇主
（employer_id，emp_type））
CREATE TABLE发布者（employer_id INTEGER PRIMARY KEY，emp_type CHAR（1）
DEFAULT''P''NOT NULL CHECK（emp_type =''P''）/ *，...其他栏目具体
到发布者* /，FOREIGN KEY（employer_id，emp_type）参考雇主
（employer_id，emp_type））
CREATE TABLE员工（.. .umploye_id INTEGER REFER ENCES雇主
（employer_id），...）
-
David Portas
SQL Server MVP
-
It would probably be a mistake not to create an "Employers" table.
Especially since you almost certainly have some attributes common to Agency
and Publisher. Otherwise splitting that entity into named tables looks like
a weak design. I would probably go for something like this:
CREATE TABLE Employers (employer_id INTEGER PRIMARY KEY, emp_type CHAR(1)
NOT NULL CHECK (emp_type IN (''A'',''P'')), UNIQUE (employer_id, emp_type),
emp_name VARCHAR(35) NOT NULL UNIQUE)
CREATE TABLE Agencies (employer_id INTEGER PRIMARY KEY, emp_type CHAR(1)
DEFAULT ''A'' NOT NULL CHECK (emp_type = ''A'') /* , ... other columns specific
to Agencies */, FOREIGN KEY (employer_id, emp_type) REFERENCES Employers
(employer_id, emp_type))
CREATE TABLE Publishers (employer_id INTEGER PRIMARY KEY, emp_type CHAR(1)
DEFAULT ''P'' NOT NULL CHECK (emp_type = ''P'') /* , ... other columns specific
to Publishers */, FOREIGN KEY (employer_id, emp_type) REFERENCES Employers
(employer_id, emp_type))
CREATE TABLE Employees (... employer_id INTEGER REFERENCES Employers
(employer_id), ...)
--
David Portas
SQL Server MVP
--
谢谢！
这些不是我的真实对象，我的实际对象层次结构更加复杂。我不能引入一个超类，因为那将是
需要多重继承。什么是我的第二个最佳选择？
Thanks!
These are not my "real" objects, my actual object hierarchy is much
more complex. I can''t introduce a superclass because that would
require multiple inheritance. What''s my 2nd best option?
>这将
需要多重继承。
尽量不要使用像继承这样的术语进行概念化，这非常好
在关系数据模型中毫无意义。关系建模不是编程的b
。如果你解释一下你的设计是如何满足你的b
要求我肯定我们可以提出替代方案。可以说，没有第二个b $ b最佳选择。如果它做对了。
-
David Portas
SQL Server MVP
-
Try not to conceptualize with terms like "inheritance", which are pretty
meaningless in an relational data model. Relational modelling isn''t OO
programming. If you explain how it is that your design doesn''t meet your
requirements I''m sure we can suggest alternatives. Arguably there is no "2nd
best option" if it''s done right.
--
David Portas
SQL Server MVP
--
这篇关于一对多扭曲的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
