不同的用户类型/对象在同一个表中拥有内容 - 如何？

Any idea how I can relate different objects together? Usecase i am trying to achieve is Comments are usually owned by a user. So i have a user_id for it. But I have company pages also where the company owns the content on its page so the owner is the company_id. (Which ofcoure is admin by several users)

知道如何将不同的物体联系在一起吗？我试图实现的用例是评论通常由用户拥有。所以我有一个user_id。但我也有公司页面，公司在其页面上拥有内容，因此所有者是company_id。 （哪个是几个用户的管理员）

One way is to have 2 tables user_comments and company_comments but the problem is then i need 2 tables per object and if i add more user types then i need to multiple the tables. What i want to achieve is 1 table which has:

一种方法是有2个表user_comments和company_comments但问题是我每个对象需要2个表，如果我添加更多的用户类型，那么我需要多个表。我想要实现的是1个表，其中包含：

comment_id PK  
owner_id (user id or company id or etc...)  - fk?

So let's say i create a owner table just to link all user types together, what would the columns be to get these all in or is there some other way?

所以，让我说我创建一个所有者表只是为了将所有用户类型链接在一起，列将是什么以获取这些或者还有其他方式？

2 个解决方案

#1

#2

People and organizations are a good example of things in a supertype/subtype relationship. They are not identical, but they are not utterly distinct. They share many attributes. Both people and organizations have addresses and telephone numbers, both people and organizations can be plaintiffs and defendants in a lawsuit, and both people and organizations can apparently own comments in your system.

人和组织是超类型/子类型关系中的一个很好的例子。它们并不完全相同，但它们并不完全不同。它们共享许多属性。人员和组织都有地址和电话号码，人员和组织都可以成为诉讼中的原告和被告，人员和组织显然可以在您的系统中拥有评论。

To implement this in a SQL dbms, put the columns common to both people and organizations in one table called, say, "Parties". Columns unique to people go in a table of people; columns unique to organizations go in a table of organizations. Use views, one per subtype, to hide the implementation details; your clients use the views, not the tables.

要在SQL dbms中实现此功能，请将人员和组织共有的列放在一个名为“Parties”的表中。人们独有的列列在人们的桌子上;组织特有的列放在组织表中。使用视图（每个子类型一个）来隐藏实现细节;您的客户使用视图而不是表。

You'd use the key from the supertype table, "Parties", as the owner of your comments. (I think.)

您将使用超类型表中的密钥“派对”作为您的评论的所有者。 （我认为。）

Here's a simplified example.

这是一个简化的例子。

create table parties (
    party_id integer not null unique,
    party_type char(1) not null check (party_type in ('I', 'O')),
    party_name varchar(10) not null unique,
    primary key (party_id, party_type)
);

insert into parties values (1,'I', 'Mike');
insert into parties values (2,'I', 'Sherry');
insert into parties values (3,'O', 'Vandelay');

-- For "persons", a Subtype of "parties"
create table pers (
    party_id integer not null unique,
    party_type char(1) not null default 'I' check (party_type = 'I'),
    height_inches integer not null check (height_inches between 24 and 108),
    primary key (party_id),
    foreign key (party_id, party_type) references parties (party_id, party_type)
);

insert into pers values (1, 'I', 72);
insert into pers values (2, 'I', 60);

-- For "organizations", a subtype of "parties"
create table org (
    party_id integer not null unique,
    party_type CHAR(1) not null default 'O' check (party_type = 'O'),
    ein CHAR(10), -- In US, federal Employer Identification Number
    primary key (party_id),
    foreign key (party_id, party_type) references parties (party_id, party_type)
);

insert into org values (3, 'O', '00-0000000');

create view people as
select t1.party_id, t1.party_name, t2.height_inches
from parties t1
inner join pers t2 on (t1.party_id = t2.party_id);

create view organizations as 
select t1.party_id, t1.party_name, t2.ein
from parties t1
inner join org t2 on (t1.party_id = t2.party_id);

Make the view updatable using whatever feature your dbms provides to do that. (Probably triggers.) Then application code can just insert into the appropriate view.

使用dbms提供的任何功能使视图可更新。 （可能触发。）然后应用程序代码可以插入到适当的视图中。