Online booking/appointment system for Hair Salon

[2desperate2usedesperate]

Hi ALL

I need to build a database in Ms Access for my school project to facilitate appointments for a Hairdresser's Salon. Customers will book appointments online only.

I'm uncertain as to how I should go about the Normalization process. I'm unsure as to what tables to create that would be easily used as a backend (Db) and web pages as the front end.


I've tables like this:-
3NF
Appointment (ApptNo(PK), ApptType)
Customer (CustEmail (PK), CustSurname, CustName, Gender, CustTelNo)
Schedule (ApptDate(PK), ApptTime(PK), ApptNo*)
CustomerAppt (AppNo(PK), CustEmail(PK), ServiceID(PK))
Service (ServiceID(PK), ServiceType, ServicePrice)

I'm not sure if this is correct and HOW do I go about setting it to be used with HTML Pages.

Thank you for any assistance rendered.

[mshmyob]

@2desperate2usedesperate
Just some things to think about for now.

I wouldn't use an email for a PK for your customer. A simple autonumber would suffice. Email addresses can be very long and who wants to type all that in. Also what do you do if the customer doesn't have an email - you do realise the majority of people in the world don't have email.

I understand what a ServiceType is but what is an ApptType?

Also ApptDate and ApptTime cannot be used as a PK in your Schedule table. The would mean that you could only have one appoint at any one time on each date. For instance if you had 3 customers wanting to come in at 11:00am on Jan 1, 2009 for a haircut you could not do it.

What I suggest you do is list out all your business rules and then see if your tables make any sense.

cheers,

[2desperate2usedesperate]

Hi there, thanks for the suggestion.
I really don't like the idea of using the email address as a primary key either. The database is for a hair salon and seeing that customers will be booking online they would not know what key to enter in the CustID field, hence I would like to use an autonumber..... but since my ApptNo is autonumber, Access tells me I can't have two fields using autonumber and the problem comes in when I use CustID as a foreign key in the Appointment Table.
Any ideas on solving that, please?
Thanks

[mshmyob]

@2desperate2usedesperate
Yeah, make CustID an autonumber in the Customer table. FK's are not autonumbers but Long Numbers that refer to autonumber PK's.

I would suggest you have your teacher teach you the basics of database design (to get the table structure proper ie: normalization) and then have him/her teach the basics of Access before getting you guys to design a web database and interface.

cheers,

[ChipR]

There is an issue here regarding customers booking appointments. Do you somehow inform the customer that they are making the 20th appointment for 10:00 AM, and you only have 10 employees?

[2desperate2usedesperate]

Thanks mshmyob,
I will seek out some more tutorials concerning the afore-mentioned.


Thanks for your response ChipR,
I'm not sure if I'm accurately answering the questioning here, but I only want one booking per hour so that's why under the Schedule table I have made ApptDate & ApptTime my primary/compound key, I'm not sure if that makes any sense. I've redone these tables a few times well but I get a new issue every time I do. Any thoughts on how I should restructure/do my tables?

[ChipR]

Using the ApptDate and ApptTime as the compound key does make sense given the restriction of one booking per hour (if you assume that will never change?). That illustrates the problem with asking for help designing the database: people who don't know the requirements intimately just can't know the proper solution, they can only help explore factors to be considered. Fortunately, the person doing the grading is an excellent source for requirements.

[2desperate2usedesperate]

Ok, Thank you very much