CODEWITHSUNDEEP
phpsqlpostgresqlcakephpdatabase-design
Loolooii
Loolooii

Reputation: 9170

How to model database where different users/roles are in a project table, while all of them have to belong to user table?

I am trying to model a database for my current project and I came across a new problem. I have a Project which is supervised by Supervisor, Coordinator and Company. So Project table has Supervisor.id as foreign key and so on. There is also Student table which contains Project.is as a foreign key (because many users can do a project). This is how it is right now. What I would like to do is to have a User table containing a column named type which allows me to see what the role of that particular user is (also student). Even though the table will contain many NULL entries, I will have far less redundant code.

However, the main reason I want to have one User table is that I am using CakePHP and it is not easy to have different models log in. Is this possible in a nice way?

Thanks

EDIT: Maybe I should say that every one of these roles will have different permissions.

Upvotes: 0

Views: 670

Answers (2)

duffymo
duffymo

Reputation: 308858

I see three tables: USER, GROUP, and ROLE.

Users would be assigned to groups, and groups given roles. I think you need three, not one.

And cardinality matters: I can see where a USER could be assigned to many GROUPS; a GROUP could have many USERS; a ROLE could be assigned to several GROUPS; and a GROUP could have many ROLES. There are many to many JOIN tables as well: 

USER <-> USER_GROUP <-> GROUP <-> GROUP_ROLE <-> ROLE

This is normalized - nothing is repeated this way. USER, GROUP, and ROLE have primary keys. The JOIN table primary key is a composite of the two IDs in each row.

Upvotes: 1

Vadim
Vadim

Reputation: 642

It depend on how you will use your associations.

Why not

For example: if you use relation to output data later and you sure, that you database scheme will not changed, than ... why not? your main targets: quality of code and speed of development, so, not matter how much columns with null you will have.

But

if you not sure in your requirements or plan to extend database scheme you can use two columns

  • supervisor_model
  • supervisor_id

which will store apropriate values: Company, 77 (I mean that 77 it's id of come Company )

Another approach

You can UUID for you supervisor_id so, it will be unique among several tables and you have not many NULL and extra columns.

Upvotes: 0

Related Questions