3

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

Improve this question
6
  • Is it possible that patients would receive more than one treatment per appointment?
    – RibaldEddie
    Jan 17, 2016 at 15:39
  • Perhaps...I hadn't considered that. Thanks :) will have to do something about that!
    – Michelle-A
    Jan 17, 2016 at 19:33
  • Could I just have 1treatment_id 2treatment_id 3treatment_id ? But the data gets pulled from the same TREATMENTS table?
    – Michelle-A
    Jan 17, 2016 at 20:03
  • 1
    Unless I'm misunderstanding you, generally, if you want a normalized schema, you wouldn't do that. Instead you would remove the treatment information from the appointment table and instead create a new table that contains the appointment Id and the treatment Id and call it something like apointment_treatments or something similar. Basically you are trying to create a many-to-many relationship between treatments and appointments.
    – RibaldEddie
    Jan 17, 2016 at 20:31
  • Ah that's it!! I couldn't get my brain to work in the right way to figure that out. So then APPOINTMENT_TREATMENTS could hold more than one treatment ID as a foreign key?
    – Michelle-A
    Jan 17, 2016 at 23:04

2 Answers 2

Reset to default
4

The names of your tables should be BUSINESSES, TREATMENTS, CLIENTS, EMPLOYEES, APPOINTMENTS. Nobody appends "_TABLE" to their table names, just as nobody appends "_Word_Document" to their word document names.

Also, we usually do not include joined fields in table designs. The fact that these fields are available is evident by the presence of the foreign key.

Other than that, your design looks fine. I would recommend against splitting the fields of any table. If you have a performance issue, look into partitioning. If you have any other table editing issue, then the problem might be in how you plan on handling it, (which makes you think that the table should perhaps be split,) and not in the structure of the table itself.

Improve this answer
6
  • 4
    There are two kinds of database designers, those that use singular and those that use plural (CLIENT vs. CLIENTS). I will not speak of those that mix singular and plural.
    – Bent
    Jan 17, 2016 at 13:23
  • @Bent yes, you are right, an alternative set of names would of course be BUSINESS, TREATMENT, CLIENT, EMPLOYEE, APPOINTMENT. I prefer plural, and I think most people use plural, so using plural honors the principle of least surprise, but singular is acceptable too.
    – Mike Nakis
    Jan 17, 2016 at 13:53
  • Hehe thanks - I wouldn't have used _TABLE on my actual database or used (JOIN) etc - I was just using those for reference in my question but I take on your point that everyone should understand that the FK would indictate this. Thanks for your comments though. Much appreciated :)
    – Michelle-A
    Jan 17, 2016 at 13:54
  • @MikeNakis I'm not sure if there is a "most people" for singular vs plural (since I think I'm firmly singular by choice) but you need to be aware of the affect on tooling - rows, specifically ORM mappings for same - want to be singular (the classes in your code) or you will spend each and every day being surprised (based on the codebase I'm currently working with having plural type names for singular objects)
    – Murph
    Jan 18, 2016 at 13:07
  • @Murph yes, you are right. When I do ORM I use singular too, of course. So, singular has probably been becoming more popular due to ORM. Judging by the way the question is worded, the OP is probably not doing ORM, but perhaps singular should be standardized due to the existence of ORM.
    – Mike Nakis
    Jan 18, 2016 at 16:17
2

You shouldn't split a normalized table just because it have "many" fields. It will not make anything easier.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.