So I'm designing a database for a project which is going to enable beauty businesses to create a profile, clients to create a profile and for the client's to be able to book appointments and for the businesses to manage appointments and their clients.
I have the following tables and fields so far(I've kept is as simple as possible on here but there is more to the tables on paper):
BUSINESS_TABLE
id (PK)
name
address
contact
about
website
TREATMENT_TABLE
id (PK)
name
description
duration
price
CLIENT_TABLE
id (PK)
name
address
contact
about
current_medical
medical_history
notes
EMPLOYEE_TABLE
id (PK)
name
address
business_id (FK)
business_name (JOIN)
availability_days
availability_hours
APPOINTMENT_TABLE
id (PK)
client_id (FK)
client_name (JOIN)
business_id (FK)
business_name (JOIN)
employee_id (FK)
employee_name (JOIN)
treatment_id (FK)
treatment_name (JOIN)
treatment_duration (JOIN)
treatment_price (JOIN)
appointment_day
appointment_time
So I think I've normalised it correctly:
Business can have many clients, appointments, treatments and employees Clients can have many appointments Treatments don't have anything Employees are not linked back to business to keep things simple for now Appointments take treatments, businesses, employees and clients
So APPOINTMENT_TABLE is the linking table and all the rest feed into it.
Apart from checking to see if my logic is right, I was also asking whether I should split up the CLIENT_TABLE into more, smaller tables as there are 11 fields under the current_medical category and 20 fields in the medical_history category and both of these can be added to at any time. I think that it'll be worth putting these two in separate tables and JOINing them to the CLIENT_TABLE for organisation and being able to amend them more easily (though a little more complex in programming).
I would be grateful to hear any opinions.
Thanks,
Michelle