How users and accounts will be modeled in the database
This ER diagram shows the database implementation of the tables modelling the users, accounts and groups.
With the accounts table, there is no seperate between if an account is a company account (account which leads a project) or a client account (the account on the receiving end of the project) as this distinction is made at run time, as descipbed in this post on modeling accounts.
When describing the diagram, I’ll skip all the trivial fields, such as first_name, email etc
Key points:
- In the users, accounts and groups tables, the primary key is simply an auto incrementing ID.
- In the accounts table, the user_id field is a foreign key of the ID column in the users table, and this represents the account owner.
- In the users table, the password will be a SHA1 hash, and it will be salted as well, more on this later
- A user can optionally have a set of contact details, but at most only one entry in that table, so the users ID will act as both a primary and foreign key
- The user/account relation is many to many, meaning each account is made up of 1 or more users, and each user can belong to more then one account. This was discussed in this post about linking users and accounts
