Database Design for an appointment system

Hi, first time here answering a question - thought it was about time i gave something back.

Q1. Is this approach correct?

OK, I see what you are doing and there are 101 different ways of doing this. What you need to do is sit down with pens and paper and sketch out what your end points are and work back, drawing / short hand the queries.

Ask yourself lots of questions like...

Can i double book? If i have a booking ID can i get start finish / times, who is the expert? If someone cancels or changes an appointment do i want to record this? Yes time slots are 60minutes but that could change how do you mange prep time before a booking or admin after? Can an expert book a holiday before slots are available to clients?

You might want to think about a 'slot' as a product/service (another table) and define it. E.g its 60 minutes, costs £x, is it open for advice on kittens, motorcycles or unblocking drains. Can any expert advise?

A booking is a start time, with a slot/product (with duration), expert id, ..... You might want to pre populate a calendar with bookings, as they are taken by clients they become unavailable......

Q2. An Expert can take a full day leave...

An expert taking time off either a day of single slot is in reality a slot that is booked by the expert not a client. Ditch the holiday table and just use the booking table and aggregate the days/slots an expert has taken off if you need stats.

BUT the biggest question to ask is, 'Am I reinventing the wheel?' Are there open source solutions out there? Search Google, GitHub and Packagist list that you can use or tweak?

I hope this helps

@dave_ac Thank you for replying to my question. I am still confused about pre-populating the calendar part (how will it work part to be specific). So considering your points, i have come up with following modifications.

1. User Table
2. Availability Table
3. Booking Table

User Table

ID | Name | Email | Password

Availability Table

ID | User_ID | Day_of_week | StartTime | EndTime | Status

Booking Table

ID | Expert_ID | User_ID | Availability_ID | Payment_Mode | Booked_Date | Booking_Type | Payment_Amount | Timestamps

I am using booking type column to track if the booking is a holiday for expert or a normal booking. Also since i can fetch the availability id for the particular expert, would i still need a separate table (Pre-populated calendar)?

@iampawan31

'pre-populating the calendar' - At some point you are going to have to 'press a button' and populate the calendar within a date range. Have a look at easyjet.com and try and book a flight in 2 years time.

Your DB structure is looking more usable. Your 'Availability' table is kinda what i mean by a calendar.

Hope that helps

@dave_ac ohhhhh. so i am only worried about the appointments booked and storing them in a bookings table. maybe i am more focused on the pre-populating the calendar part, but should i populate the table with a full calendar?

I would do something like this...

Users Table (customers and backend users)
id, username, password, role, type

Profiles table (in case you have a parent that wants to book two kids so they can create as many profiles as they want)
id, user_id, parent_id, name, type_id, etc  

staff_availability (this will be a type_id in the profiles table)
id, profile_id, day, time, status, etc..

Booking
id, profile_id, date, time, duration (you can also do start-time  and end-time), gmt, status, note, etc

Invoice
id, booking_id, price, currency

you can do it 102 ways i guess. Best thing to do is that you create use cases and reporting requirements.