Database schemas/relationships

I am trying to create a simple schema that would satisfy the following and have been struglling to get it right it would look something like this:

A User that has a role, that belongs to a department, however many roles can have many departments but many users can belong to only a single role e.g a user cannot be in accounting and hr. I have been trying to build this sort of relationship for a while but cant quite get my head round it, any assistance would be awesome :)

Also would this be possible to write in a DB seeder ?

siangboon

4 years ago

Level 54

the description seem confusing not other people but yourself also I believe as there seem conflict each other.

perhaps you have make it more clear what you try to do and what the definition of role, department and perhaps title/position, it may make it more clear...

Fioreski

4 years ago

Level 2

Hey sorry the description is a little confusing, essentially trying to create a DB schema in which you have employees , each of which has a role. Then departments of which have employees and a department head. Each dept will also have a set of employees which will need roles.

Dept heads can add new employees with a single role but departments will have multiple roles.

I hope that makes sense.

automica

4 years ago

Best Answer

Level 54

@fioreski I would break down your statement above into simple model relationships, and just worry about each bit at a time.

I get the following

Role belongsToMany Department
Department hasMany Role
User hasOne Role

or something similar?

Which would mean User hasOne Department Through Role.

https://laravel.com/docs/8.x/eloquent-relationships#has-one-through

Don't worry about

Dept heads can add new employees with a single role but departments will have multiple roles.

yet. That bit relates to permissions and that's a separate layer to deal with after the ORM is sorted.

Fioreski

4 years ago

Level 2

Thanks this is really helpful. I find thinking about DB stuff quite hard to visualise/break down. Is there any sought of helpers you could suggest when building them or is it something that just comes with time?

Please or to participate in this conversation.