Whats the best practice to save these data

You're probably better storing them as datetime's or just integers - then the algorithm is easier? Depends exactly how you're planning to check the availability I guess though - if it's really 'any monday, 9-5' then you'd maybe do it differently :-)

@ohffs : I just want to see if anybody is available for a day ( may be Monday) in a given period of time ( 9-12). When I ask for availability to the user, all I'm asking is just which day of the week and the time.

If you use mysql database, you can store these data as JSON data. See https://dev.mysql.com/doc/refman/5.7/en/json.html

@kadari In that case, how would I be retrieving specific rows that fall in the given criteria?

a user can have many availability and a availability belongs to a user... https://laravel.com/docs/5.2/eloquent-relationships#one-to-many https://laravel.com/docs/5.2/eloquent-relationships#querying-relations

@poorcoder : How should I save the available days? As integers or string? In the same row or multiple rows for each day?

You can use Functions that search JSON values: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

If you want to avoid from JSON fields, then better to use a table for free periods (day_from, day_to, time_from, time_to) and use a SELECT something like this: SELECT from free_periods where day_from <= $day and day_to >= $day and time_from <= $time and time_to >= $time

the pivot table can be like this. You can use enum type for days, from, to...

id  | user_id   | days  | from  | to
1   | 1         | Mon   | 09:00 | 22:00
2   | 1         | Tue   | 13:00 | 22:00

Soo you can get user's all days...

@poorcoder , yeah this was what I had in mind. I was just wondering if this is the best way to store data. In this way there might be more than 20 rows per user.

If you use day_from and day_to fields in your database, then you need only one record for Monday-Friday 9:00-12:00. Represent the day of week by a number (Monday = 1, Tuesday = 2, etc.) Also it is easy querying the table:

SELECT FROM free_periods WHERE user_id = $id AND day_from <= $day AND day_to >= $day AND time_from <= $time AND time_to >= $time;

Hi there, what kind of queries do you want to make to that structure? Per week check available days? Mounth? Year? Several user/timetables at the same time?

@sapagat I want to get all users available for the given time. Let say I want to get all users available for the time Mon and Wed between 5:00PM to 8:00PM This is what I want to query basically.

Take a look to Unix timestamp, it is relative to the amount of seconds from a certain date that I can't remember xD. Then your query will be just the users that don't have an appointment between timestamp_0 and timestamp_1. It will take into account day, time and everything (even seconds).

Take a look to carbon for server side helpers (check docs) with timestamps and moment js for client side.

In my opinion you could implement some mutators (check docs) so that when you serialize the result the work at the client side will be easier.

Take into account that what will determine the best solution will depend on your application. Hope this helps.

@JillzTom I think your idea of how to store it is just fine. I might consider using an enum vs a text field though.

Let's pretend user is the person setting up free time and customer is requiring users for set times.

I would create a avalible table with user has many. Each day is one row with: User_id - integer day - date start - integer end - integer

Start and end would be times, but as military time (24 hr) so 1530 would be 3:30 pm. Integers are imo easier and have better performance. Now where this may break down is more then one time slot per day. Which case another table to hold the times.

Carbon will be your best friend here as you can easily set a user to have every Monday or every other Monday etc and able to get dates along with creating and formatting dates easily.

Some things to try for performance would be to grab all users for a specific date or dates that a customer needs, then loop through with php to see when start and end times are with in range vs using a query. I found between in relational db slow vs php.

Any case I built an event app that a customer can search for events by a date and by date and time that performance was horrible by MySQL between but was great with php. It also was very similar to what you have described.

I am not a fan personally of elaborate pivot tables rather just a plain old connection table linking two to three tables. In the end you generally have the same amount of data stored in your db and with proper indexing no measurable performance upgrade and using complicated pivots a lot more complication then needed.

Break it down to each part: User is avalible for this date. User is avalible for these dates. Customer requires a users for this date. Customer requires a users for these dates. Then worry about times avalible. By then, if your using tdd, it's just refactoring a little bit to get users at certain times.