Different user types / objects own content in same table - how?

Question

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)

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:

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?

Answer 1

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.

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.

Answer 2