Not sure you need a days table? Wouldn't timestamps work easier?
Then your relationships would be:
bookings
..hasOne user
..hasOne machine
..hasOne slot
machines
..hasMany bookings
users
..hasMany bookings
slots
..hasMany bookings
This should work, depending on what your logic is for your app. I wouldn't overdo it with the relationships from the get-go. Not everything needs to be accessed through everything else. A good check is to write some pseudo code of what you data you might be looking up (like how busy a certain machine gets or if a slot is way more popular than the others, for example).
Interesting question :) Is it necessary to have "days" table?
I would create "slots" table with all possible slots for all machines available through a day (assuming all machines have same slots per day)
Slots:
"id"
"machine_id" - foreign key to machine
"timeframe" - some basic unit time (1 hour?) like 9.00 - 10.00
"weekend" - boolean (this indicates if slot is available on weekens)
Bookings:
"id"
"slot_id" - foreign key to slot id
"date" - exact date of desired booking (in a DD-MM-YYYY format or similar)
"user_id" - foreing key to user id
All this will be correctly set in models with all relationships etc...
User has many bookings
Slot belongs to one machine
Machine has many slots
Booking belongs to user
Booking belongs to slot
So when you want to display all slots for all machines in current day
$slots = Slot::with('bookings')->get();
@foreach ($slots as $slot)
@if ($slot->booking && $slot->booking->date == sysdate)
Slot {{ $slot->timeframe }} on machine {{ $slot->machine->machine_name }} is not available
@else
Slot {{ $slot->timeframe }} on machine {{ $slot->machine->machine_name }} is available
@endif
@endforeach
When user create a booking you simple create a new record in Bookings table with user_id, slot_id which is also exact indiciation of machine as each slot belongs to only one machine and date (without time) of a booking.
When you want to display all bookings for a user
$user = User::find($id);
@foreach ($user->bookings as $booking)
User: {{ $user->username}}
Machine: {{ $booking->slot->machine->machine_name }}
Date of booking: {{ $booking->date }} at {{ $booking->slot->timeframe }}
@endforeach
I hope I did not miss something all this above is not valid :D
I need a days table because a machine can have more slots on weekends. Eg:- Machine 3 can have 2 slots on Tuesday and 5 on Saturday. Somewhere it should be predefined.
And bookings table will have many to many relationship, I believe. A user can book a slot every week. So users might book a same machine, same slot, the next week.
EDIT: Sorry. One particular booking belongs to a user.
If your slots are like such:
id: 1, day (enum): 'Monday', time: 10:00:00, duration: 2, machine_id: 2
id: 2, day (enum): 'Monday', time: 10:00:00, duration: 1, machine_id: 3
...
I still see no reason for a days table. What extra info do you need?
If bookings has manyToMany that means a booking can have multiple users? That would mean multiple people using the same machine at the same time.
Thank you. Your reply must give me a head start. :) I will get back if I run into issues.
@ beaverusiv
Sorry, my bad. The booking doesn't have a many to many relationship.
Well, days table is unnecessary, I understand.
Thank you for your help.
@drtechie no problem... only setback i see with above, is how to quickly see if slot is taken or not in particular day.
I wrote quickly something like
if ($slot->booking && $slot->booking->date == 'some date')
but i don't like it. Maybe some helper function, or some function in model to check if a slot belongs to some record in bookings table for some date and return false or true, will be convenient.
If slot belongs to some record in bookings table for some date it means it is reserved for that date.
If slot is not present in bookings table for some date, that means it is free
Ya, I understand. I wanted to know the basic database structure. There are other constraints like a user must have only one booking per week etc. I have to make a lot of helper functions anyways.
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast