Best datastructure for this relationship...

Question

I have a question about database 'style'.

I need a method of storing user accounts. Some users "own" other user accounts (sub-accounts). However not all user accounts are owned, just some.

Is it best to represent this using a table structure like so...

TABLE accounts (
 ID 
 ownerID -> ID
 name
)

...even though there will be some NULL values in the ownerID column for accounts that do not have an owner.

Or would it be stylistically preferable to have two tables, like so.

TABLE accounts (
 ID 
 name
)

TABLE ownedAccounts (
 accountID -> accounts(ID)
 ownerID -> accounts(ID)
)

Thanks for the advice.

Answer 1

I would keep the tables separate.

Self-referencing foreign keys can cause a lot of pain to update/delete.

With the combined table, a cascading delete on the owner will then delete all the owned accounts. (Which may or may not be desirable.) With the separate table, a cascade delete will only delete the relationship that the accounts were owned, and not the accounts themselves.

Answer 2

2nd choice is the best.

On a theoretical point of view, it is a regression of a 'n-to-m' (or 'many to many') relationship, where 'n' and 'm' side tables are merged in one unique 'Accounts' table, and where 'OwnedAccount' table is the linking table.

Using this model will allow you to implement data integrity rules at the server level without any problem. In addition to #mdma arguments, it will also make querying and reporting a lot easier. Further 'extensions' of ownership rules (multiple owners for one account, cascading ownership) could also be implemented in this model.