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.