2

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

  • Is it possible that patients would receive more than one treatment per appointment? – RibaldEddie Jan 17 '16 at 15:39
  • Perhaps...I hadn't considered that. Thanks :) will have to do something about that! – Michelle-A Jan 17 '16 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 '16 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 '16 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 '16 at 23:04
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.

  • 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 '16 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 '16 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 '16 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 '16 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 '16 at 16:17
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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