Figuring out database structure

Posted 3 years ago by francoboy7

Hi everyone I am working on a website idea. It's for my company. It will allow the users to swap work shifts. For example : Marc (user ) doesn't want to work On the 26th of august from 11AM to 11PM.

He is free on the 27th of August and the 29th. Marc (user) is then looking for someone (let's say Adam(another user)) who would work for him on the 26th and give Marc one of his shift that he has on the 27th or 29th of august. it be accurate to say that shifts have a start and end datetime. Users enter certain shifts they don't want,and in order to reassign a shift between users, a transaction is made by 2 users each providing a shift they belong to where the user assignment is switched.

The schedules are always changing (ambulance crew) some full time, some part times. This service would be just to swap some specific date (always changing) For example I am booked on 26th and got a last minute appointement. So I'm basically looking for another coworker to take my shift. However I can't just "give" him my shift, I need to swap a shift with him. We get our schedule monthly but they change afterward

Multiple questions arise from this as I'm new to Laravel:

1 - How should (could ?) I create my databases (especially the one concerning dates)
2 - Should I create a "Dates" table containing column like :"user_id", "date_to_swap","date_available_to_work" (and should I then separate the dates with a comma or period if the person has a lot of free days that he could swap)
3 - Will I need a pivot table (I'm pretty certain I will)
4 - Is there any "date" packages that can help me manage more easily (should it be front-end or back-end)

As I said I'm really new to this, and I'm just starting to comprehend Laravel so this is basically to open my mind on some ways to realize my project (because developers all have their different ways.) And for clarification I'm using Laravel 5.1

I thought about something like this

user_id | date_to_swap

 1    |  12-07-2018  
 1    |  17-07-2018  
 2    |  13-07-2018
 3    |  12-07-2018  
 3    |  17-07-2018

But then I'll need another table for the date that he is able to work (for the swap) no ?

Thank you all for your help, I hope I'm clear enough...

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.