#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    11
    Rep Power
    0

    Appointment Scheduler Issue MYSQL


    Hi. I am currently in the process of creating a Delphi application which will be used to keep track of appointments and customer details.
    I have created a MYSQL database with the following tables:

    - Customers (id, Name, DOB)
    - Addresses
    - Appointment Types (id, Length)
    - Appointments (id, Start, Finish)

    The 'Appointment Types' table refers to various things that can be booked. The stored Appointments in the 'Appointments' table should be as follows: i.e: a 'client' can book a 'haircut' at '4pm' on the date '07/07/13'.

    The appointments are carried out by one user, therefore appointment times would always be unique and would never clash. Would I need to create a new table (Schedule), which stores all the Appointment_id's and then transfer these values onto a StringGrid/DBGrid in my Delphi application?

    Or am I going about storing the appointments in a completely wrong manner? I am confused as to how I should store appointment slots in a MYSQL table and how I would link these rows with other values, such as customer_id and appointmenttype_id.

    I have had a read around on older posts on a similar topic, however I am finding a lot of them hard to understand. Thank you in advance for any assistance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    presumably, you want to have some way of associating the tables with one another.

    for example, you'll want to associate an appointment with an appointment type and a client. there are 2 basic ways to do that.

    one is to have separate tables for the relationships and the other is to include some type of reference column in one table for the other.

    for example if you added two columns to the Appointments table so that it is:
    Appointments (id, start, finish, type_id, customer_id)

    You can use joins to show which customer and type is associated with which appointment.

    this is usually a better option.

    However, if you have a one:many relationship between the tables (e.g. if one appointment can involve two different types), it is often easier to have a relationship table.

    for example:
    Appointments (id, start, finish, customer_id)
    Appointment_Type (id, length)
    Appointment_To_Type (appointment_id, type_id)

    in this method, you can use a join to show all appointment types associated with the appointment.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    11
    Rep Power
    0
    Thanks for the reply.

    I'm currently altering my MYSQL tables using the EER Diagram within Workbench. I initially had a diagram which linked the following:
    • customers - appointments (one : one)
    • appointment type - appointment (one : one)
    • address - customer (one : many)


    Am I doing this correctly? I intend to allow an appointment to be made with the option to add multiple types (haircut, nails) per appointment_id. In addition, I intend to allow multiple customers to have the same address.

    Would I need alter my current relationships? I'm sorry if this is an easy question, it's just that the relationship stuff confuses me.

    Also, If I were to use your second option of having a middle table linking the appointments and types, how would I go about linking these using the relationships in the EER Digram view.

    I am getting confused as I am having second thoughts when linking tables, due to the new keys being created. For instance, my appointment table currently has customer_id and type_id as foreign primary keys. Is this correct?

    Thanks once again
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by Master A
    Am I doing this correctly?
    not quite

    if, for example, customer-appointment was really one-to-one, then each customer would only ever have one appointment, ever

    likely it's one-to-many

    here's a hint: for each side of a relationship, identify how many of the other side can participate in the relationship

    so... a customer can have one or more appointments, but an appointment is only for one customer

    make sense?

    Originally Posted by Master A
    For instance, my appointment table currently has customer_id and type_id as foreign primary keys. Is this correct?
    yes, most likely

    can a single appointment ever be for more than one customer? can a single appointment every be for more than one type?

    if the answer to both of these is no, then you're doing it right

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    11
    Rep Power
    0
    I understand now Thank you

    I think I'll only allow each appointment to store one appointment type
    Therefore, if nails and an haircut are booked, they would be treated as two separate appointments.

    Referring back to linking the currently booked appointments with my Delphi program, what would the easiest way to transfer these bookings to my program be?
    Should I just send all the appointment id's to my program and then code some sort of algorithm to transfer these onto a grid, corresponding to their start and finish times?

    I appreciate that this is a MYSQL section, but if you could help me to decide how to store these appointments in MYSQL (if the current appointment_id's method isn't appropriate), I'd appreciate that.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    11
    Rep Power
    0
    Sorry, one more thing.

    My relationships are currently non-identifying.

    Would this effect the linking of data, or would it just mean that the data does not HAVE to be stored.
    (i.e: appointment doesn't have to have a customer_id)
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by Master A
    Would this effect the linking of data, or would it just mean that the data does not HAVE to be stored.
    (i.e: appointment doesn't have to have a customer_id)
    not sure what you mean by this

    "non-identifying" simply means that the FK (from, say, appointment to customer) is not part of the appointment PK

    this is perfectly okay (the alternative is that the relationship is identifying, so that the PK of the appointments table is a composite key and includes the customer_id)

    but imagine that the appointment didn't have the customer_id... how would you know which customer it's for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    11
    Rep Power
    0
    I see. I know I would always need a customer_id in my appointment table.
    I'll get my program to validate the checks for that.

    Thanks for explaining clearly.

IMN logo majestic logo threadwatch logo seochat tools logo