help with using time in sql

[tinkle]

Hi this is my first computing project for A-levels to create database for a website which allows me to book salon appointments, (using phpmyadmin)



each customer is registered and can book apointments on when to have there appointment and by which salon specialist, although one salon specialist(staff)can only serve one customer at a time.

*** for example betty books a a appointment at 9:30am-10:00am with staff member cathrine

so the tables I have are:

Customer, Staff, Booking
-----------------------------------------------
Customer: CustomerID (P), password, fname, sname

Staff: StaffID (P), fanme, sname

Booking: BookingID (P), CustomerID, StaffID, ???BookingTime???
----------------------------------------------------------

^Does this look ok? I really don't know how time would work in this table with mysql, would it be better to have time as a seperate table or to have time start and end for each staff member?


major thanks for replies

[SeoKungFu]

I say you use this : MySQL :: MySQL 5.1 Reference Manual :: 10.3.1 The DATETIME, DATE, and TIMESTAMP Types
Datetime as type of your bookingtime column will do you ok, take a look mysql's got plenty of useful funky functions for these operations, including good for search, period snapshoting and math-like operations with time data.

[crivion]

that structure's ok I don't know why you want to keep it separate

[johndesenio]

You need to add another field in Booking table. If there is a field BookingTimeIn you should put a BookingTimeOut to determine your done in appointment with your customers. The datatype for BookingTimeIn and BookingTimeOut is datetime.