1

I had a question about the best way to handle this type of data.

In my system, I will have many users and many accounts... users are able to belong to many accounts, and accounts will have many users. I'm guessing the best way to accomplish this is with three tables

users

accounts

& users_accounts

my question is, when someone signs up for a paid account... where should I store the flag that distinguishes between regular users of an account and account holders? Should there be an owner flag in the users_accounts table?

JMax
  • 26,109
  • 12
  • 69
  • 88
ThinkingInBits
  • 10,792
  • 8
  • 57
  • 82

3 Answers3

1

Can I assume that one account cannot have more than one user (1-to-many relation)? In that case, two tables would be sufficient:

  • users
  • accounts

Where accounts contain a reference to a user id. A separate relationship table would be superfluous when there is no many-to-many relation.

Then the question arises: can a user have both paid and unpaid accounts? If so, the flag belongs in accounts. Otherwise, it belongs in users.

Taking your clarification into account, your three tables design is appropriate. The answer to your question then completely depends on how you want paid accounts to work.

  • Will a paid user have extra functionality in all accounts? Then the flag belongs in users.
  • Will any user have extra functionality in a paid account? Then the flag belongs in accounts.
  • Will a paid user have extra functionality only in paid accounts? Then the flag belongs in users_accounts.

If every account has only one owner, then you should put a user id representing the owner in the accounts table.

mhelvens
  • 4,225
  • 4
  • 31
  • 55
  • Sorry, I guess I didn't clarify. Each account will also have many users. – ThinkingInBits Nov 23 '11 at 15:30
  • The user wouldn't have a paid and unpaid account persay, but they would be the 'account-holder' so to speak, or an 'account-member' – ThinkingInBits Nov 23 '11 at 15:32
  • In that case, I would stored the flag in the account table. If the account is paid, all users are paid, right? – Ed Manet Nov 23 '11 at 15:33
  • My question is how to determine who is the owner of the account. – ThinkingInBits Nov 23 '11 at 15:35
  • Your third bullet-point is what I'm getting at. The paid account holder would only have special abilities within the account he's paid for... but he may belong to someone elses account as just a basic user. – ThinkingInBits Nov 23 '11 at 15:36
  • Ah ok, I think I was over thinking it. You're right. A simple owner_id in the accounts table should suffice. Thanks for helping me think this through. – ThinkingInBits Nov 23 '11 at 15:39
  • 1
    See my latest edit. If an account can have only one owner, put a user id in the `accounts` table. You can also put your flag in `users_accounts`, but then it's harder to enforce the one-owner constraint. – mhelvens Nov 23 '11 at 15:39
  • So I could do a 'type' column in the users_accounts table that would specify each users role within an account. – ThinkingInBits Nov 23 '11 at 15:43
  • That depends on how sophisticated your type/role system will be. If it's as simple as one owner per account, stick with the user id in `accounts`. If different users can have the same role within an account, then your 'type' column idea would work well. – mhelvens Nov 23 '11 at 15:46
0

Users table will have only the user related data... name, surname, etc... accounts will have the info of the account... type and any other data...

The key is that users_accounts is a relation table between Usersand accounts so it will have any linking data from the Users to the accounts and THERE you should put the flag because is there when you set the relations.

admin
  • 41
  • 1
  • 6
  • there as in the users_accounts table? – ThinkingInBits Nov 23 '11 at 15:34
  • I get confused with the table names... users_accounts is teh relational table and there you should put the flag... The structure should look something like this: `id - id_users - id_accounts - flag` – admin Nov 23 '11 at 15:37
0

Add a paid flag to user_account.

Example Attributes:

  • user (id, name, street ...)
  • account (id, name ...)
  • user_account (user_id, account_id, paid)

You can tell by the paid column if the user is a premium account member or not.

trapp
  • 86
  • 4