One-to-one and one-to-one relationships in the same tables?

Question

I am developing a database in which two fields have a one-to-one relationship, but I also need a one-to-one relationship between them, and I would like some advice on whether there is a better way to do this is what i got right now.

My tables: accounts and users . An account can have multiple users, but each account can have only one owner. A user can only be associated with one account.

I have an account field in the users table, which stores the identifier of the account to which the user belongs. In the accounts table, I have the owner field, which stores the identifier of the user who owns the account (i.e., the main administrator).

I use InnoDB to use foreign keys. The problem is that I cannot create an account or user without creating another one created first (due to foreign key restrictions), so I made owner nullable. Now I can create an account with owner zero, then create a user, and finally set owner in the account for the user.

Is this acceptable, and is there a better way?

Here are some possible other ways that I came up with, and my thoughts on each of them:

• Enter the logical owner field in the users table. Since each account can have only one owner, this method seems less ideal because I have to ensure that only one user per account has an attribute set to true .

• You have a third table named owners . This seems like a lot of overhead and more work for no good reason, since it is actually the same as having the owner field in the users table.

As for me, this is of most importance to me now, but it’s a little inconvenient to set the owner to zero until I create a user, and then come back to set it after the fact.

I would be grateful for any materials that you can give me. Thanks!

This question is similar, but there are no references to foreign keys: Designing tables: from one to many and from one to one at the same time?

Answer 1

In general, this is a bad idea if your circuit cannot be sorted topologically, i.e. if you cannot establish the order in which the table refers only to the tables preceding it in the ordering. Such a "multi-level" dependency is also a very nice feature, for example, for software modules (you have a problem if two modules depend on each other).

In your case, you have a user who refers to an account and an account that contacts the user so clearly that there is no way to find a topological ordering.

One standard solution in this case is to introduce a separate table, for example. "role" where you have three columns: user, account and role. The role of the column can be "owner" or "guest".

The fact that you know that (subject to current requests) an account must have one and only one owner or that a user must be specified in one and only one account are not IMO rules that really apply to the users domain "and" accounts ".

You can easily implement these rules, but structuring your data so that you do not have another opportunity is an IMO error. You should strive to model the domain, not specific rules ... because people will change their mind about what the rules are.

Can you conceive a user with two accounts? Can you conceive an account with multiple owners / admins? I can ... and that means that most likely it will be a request soon. Structuring the data so that you cannot represent it is looking for problems.

Also, when you have circular dependencies in the model, your queries will be more difficult to write.

A very common case is, for example, an attempt to present a database of a list of hierarchical parts using only one table with a "parent" field that points to the table itself ... it is much better to have two tables instead, a part and a component where the component has two references per piece and quantity.

Answer 2

Your decision is in order.

If you are not comfortable when the owner column is NULL, you can rely on some kind of user magic entry (possibly with a zero identifier), which will be a "system user". Thus, the newly created accounts will belong to the user-zero until their property is properly redefined. This seems more enjoyable than allowing accounts to have zero owner, anyway.

Answer 3

For the current requirement, have only one user account

 alter table UserAccount add constraint un_user_account unique(UserID); 

and when the requirement changes to many-to-many, remove the restriction

 alter table UserAccount drop constraint un_user_account; 

For just one owner, just apply this at the application level.