database structure for "multi-account" app

[eluser]Skinnpenal[/eluser]
Hi!

Most web apps has an "paying account" which all the data and "sub users" belongs to.

I wonder how this is best solved?

Does every table in the database have a column like "account_id" that has the id of the account the data belongs to?:

Code:

[TABLE: COMPANY]

id
account_id   (what account id)
name
.
.
.


[TABLE: NOTES]

id
account_id   (what account id)
title
text
.
.
.

Or.. Should one have a dedicated table for defining the relationship?:

Code:

[TABLE: ACCOUNT RELATIONS]

account_id   (what account id)
data_id      (what data id)
data_table   (what table is the data in)

..But having the actual table name can't be a good solution?

Well.. as you see, I'm obviously lost :p Please share your views

[eluser]Jondolar[/eluser]
I don't think you need to have all tables with an account ID but you do need to think about your data structure and determine which tables are top-level. Basically, if you had a table called orders and a table called orderitems then you'd need to make sure the orders table has an account ID but your orderitems would not because they would only ever be associated with an order or can easily be selected with a join on the order table.

Another example might be a products table would have an accountid but a productoption table wouldn't because it would always be associated with a product in the products table.

[eluser]Skinnpenal[/eluser]
Yes, that's a good point. So at least I won't need this column on *every* table.

What about the idea with a dedicated table for the relations? No good?

[eluser]Jondolar[/eluser]
I don't think that it is ever a good idea to have a separate table manage your relationships between tables, especially, among multiple tables.

I'd recommend putting in your accountid into the top level tables and leave it at that. You could put the accountid in every table except generic lookup tables if you wanted to. Tables like "states" wouldn't have any accountid but orderitems could. It might make a few queries easier like "count all products" where you can set the accountid and not have to worry about joining the orders table.

[eluser]Colin Williams[/eluser]
If you have a one-to-many relationship, use a foreign key. If you have a many-to-many relationship, use a relationship table (but have one relationship table per relationship, not one table to handle all relationships across tables).

[eluser]Jondolar[/eluser]
That was much better said. I wish I could explain things better. Nice job.

Just remember that your foreign key is really going to be two fields (id and accountid).

[eluser]Skinnpenal[/eluser]
Ah ok, I see. These are issues I tend to run into on all projects, but now I know what to do

Thanks a lot to both of you, Colin and Jondolar, for your replies!