Data-modeling problem: weak entity owned by two unrelated tables

Suppose I have the following tables: "User", "Computer" and "Command". Users and computers may have 0 or 1 commands associated with them. A command is always owned by one (and only one) of these two. How can I model this?

I thought of something like this:

user(id开发者_Python百科, command_id?, other1)
computer(id, command_id?, other2)
command(id, other3)

This guarantees that user/computer can have 0 or 1 commands, but allows for a command to have more than one owner.

But since command is a weak entity (I suppose, please correct me if I'm wrong), it should reference its owner in a foreign key. This leads to the following structure:

user(id, other1)
computer(id, other2)
command(id, owner_id, owner_type(user|computer), other3)

However this allows for a user or computer to own more than one command. It also doesn't look as nice as the first option.

Suggestions?

You can use specialization.

User(id,other1)
Computer(id,other2)
Command(id,other3)

User_cmd(user_id,cmd_id)
Comp_cmd(comp_id,cmd_id)

I dont believe there is a good way to do this with standard SQL. There may be a DBMS out there that provides a good solution that I am just not aware of.

I have seen both of your solutions used in the past. I really dont like the second solution because you are not able to use true FK relationships. When you define a FK constraint, the table must be specified. However, the table is determined by what is in the owner_type column. Additionally, if a third owner type was defined, you must introduce another string to represent your model. This begins to get messy.

I prefer the first solution but with an is_owned boolean column on the command table. This field tells me if someone already owns the command. The downside is that I dont know who owns the command simply by looking at the command record. The upside is that I can define a true FK constraint and if I ever have a third+ owner_type I can simply add the additional table without any changes to the existing models.

It sounds like the domain of command is a set that is the union of users and computers. You could add an artificial entity that represents this set, such as command_owner:

user(id, other1)
computer(id, other2)
command(id, other3)
command_owner(id, user_id, computer_id, command_id)

You'll need uniqueness constraints on command_id, user_id and computer_id in this table. But this introduces a new edge case where a row might get computer_id and user_id specified, which I don't think is allowed by your model. You'll need some sort of out-of-model rule to enforce this, like a trigger or programmatic constraint.