Please check whether my ERD is relational

[JackieFeng]

I am creating a booking system. Is this correct erd? Also I would like to ask should i create another table for time slots?

[CJ_London]

looks OK to me in principle. Without knowing anything about the business...

suggest you don't need the endtime field in tblBooking if it is calculated on the basis starttime+duration

also you may need provision for additional times 'booked out' which are not massage related - e.g. maintenance

ditto for the availability of masseuse - e.g day off/gone to dentist

you may want an additional table which shows which types of massage can be undertaken by which masseuse so the user can't allocate an untrained masseuse.

the one to many relationships you have defined means all the tblbooking FK's needs to be completed to save the record - what happens if for example you know the room, customer and type of massage, but don't know which masseuse?

also consider having a username and timestamp field in tblbooking so you know who took the booking - and consider the situation where bookings change - do you need to keep a history?

[JackieFeng]

Thank you for replay. The business is about the user(receptionist) books an appointment for a customer who wants massage.

You mentioned there is no need for endtime field but i need to check the availability of masseuse because for example between 9am and 930am, particular masseuse may do massage for an customer so my question is how to calculate the end time using start time+duration or what tables i need to create?

Also how do i show the availability of masseuse in the masseuse table e.g they do not work at 1pm which is lunch time or some masseuses do not work at Wednesday

If one masseuse can do two massages, which table should store this information? or how should i name 'additional table'.

Is it possible if you sketch erd to show how would you link tables for me.

[JackieFeng]

Thank you for replay. The business is about the user(receptionist) books an appointment for a customer who wants massage.

You mentioned there is no need for endtime field but i need to check the availability of masseuse because for example between 9am and 930am, particular masseuse may do massage for an customer so my question is how to calculate the end time using start time+duration or what tables i need to create?

Also how do i show the availability of masseuse in the masseuse table e.g they do not work at 1pm which is lunch time or some masseuses do not work at Wednesday

If one masseuse can do two massages, which table should store this information? or how should i name 'additional table'.

Is it possible if you sketch erd to show how would you link tables for me.

[JackieFeng]

Need more suggestions plz..

[CJ_London]

so my question is how to calculate the end time using start time+duration or what tables i need to create?

no need to create additional tables - you would use the dateadd function - endtime=dateadd("n",duration,startdate) assuming duration is an integer datatype expressing number of minutes

how do i show the availability of masseuse in the masseuse table e.g they do not work at 1pm which is lunch time or some masseuses do not work at Wednesday

this would require a separate table with masseuseFK plus fields to either when masseuse is available or when they are not (plus why not) - which way is up to you and also depends on how you want to store the info (i.e. 1st to 14th June (holiday). For the latter, something like

tblNotAvailable
NotAvailablePK
MasseuseFK
date/timefrom
date/timeto
reason

for repeating days off, (e.g. every

If one masseuse can do two massages, which table should store this information? or how should i name 'additional table'.

not quite sure what you mean - at the same time? two different types of massage? - for the former use the tblbooking table, for the latter you need a separate table

tblSkills
SkillPK
MasseuseFK
MassageFK

both examples, have one (tblmasseuses) to many relationship - masseuseID to MasseuseFK

I also strongly recommend you lose the spaces in your field names - they will only cause additional work. Use underscore instead if you must.

[JackieFeng]

Thank you so much!!! This is my new ERD.

Just two more questions. tblmasseuse and tbltypes(massage) are many to many relationships. What fields should i put in tblskills. e.g Masseuse 1 can do back and deep massage but Masseuse 2 can only do deep massage. How do i represent these information.

Second question: For the start time, the user selects time from a drop list in 'formbooking'. I have seen some examples that have 'tbltimeslots' If i need this table, should i link to 'tblbooking'?

[CJ_London]

1. you would need to differentiate between the three types of massage - deep, back and deep & back. If you are saying that you have two types (deep and back) and these can be combined into one massage then you need another table to create a many to many relationship between tblbooking and tblTypes.

2. not really clear what you are asking - I would expect you to have a dropdown or list of times set on fixed values - 08:00, 08:30, 09:00 etc for the user to select a time so no need to have a tbltimeslots - it is effectively your tblbooking. However there are many ways to do this and what works for you depends on the way your processes work, the order of conversation with the client, what information you are providing on screen, etc

[JackieFeng]

1. The shop provides five types of massages and masseuses can do all massages. This means tblmasseuse and tblmassage are many to many relationships so i need a third table to connect those two tables. I would like to ask what fields i should put up on that table(tblskill maybe?)

2.You are right. I would expect to have list of times set on fixed values like 9:00,9:30,10:30 for user to select. You said there are many ways.. Can you suggest a good way for me?

[orange]

JackieFeng,

An ERD is based on a description of your business and the business rules.

Here is a link to some business rules at Barry Williams' site.
And here is a link to a database design approach at his site.

You may get some ideas from this free data model. It deals with Hairdressers and Customers and Appointments, but you could probably change the names to meet your terminology.
Also note the links on the data model to a Physical Data Model and The Facts that led to this model.

Good luck.