Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Database design? How should I?

    I want to create a database for a business to keep a record of client bookings.

    I want them to be able to book appointments between the hours of 0001 & 2359. Then I want staff to be able to view appointments for a partular day. They appointments would be booked in blocks of 15 minutes, i.e. 2 15 minute blocks would be booked for a 30 minute appointment.

    But what structure should the database table take?

    I was thinking of a colum for each 15 minute block, with either a '0' for not booked, and a '1' for booked, with a reference to who has booked the appointment i.e. contact id, then details are pulled from a table containing contact details.

    table example
    Booking ID, 0000-0014, 0015-0029, 0030-0044, contact ID, Appointment description

    does anyone have any better ideas that would make this simpler?
    Last edited by fuelinjection; 02-05-04 at 04:46.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Bookings(Booking_id, Start_time, End_time)

    The business requirement that appointments are made in 15-minute blocks can be enforced through a DBMS constraint.

    Oracle,
    MOD((END_TIME-START_TIME), 15) = 0
    Last edited by r123456; 02-05-04 at 04:50.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    I want to be able to display the bookings for a particular day in a table, with timescales next to each row, then the name of the person who booked the appointment in the next row, with each cell coloured depending on whether the time block is booked or not.

    I should also mention that this is going to be a PHP, mysql, website.

    example.

    Time Booked for
    09:00
    09:15 Mr Smith
    09:30 Mr Smith
    09:45
    10:00 Mrs Jones
    10:15 Mrs Jones
    etc
    etc
    etc

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Table:
    Bookings(Booking_id, customer_id, Start_time, End_time, day)

    Select *
    from bookings
    where day = 'x'
    order by day, start_time;
    Last edited by r123456; 02-05-04 at 05:06.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    so what your saying is, design the database first, then use formulas as scripts to get the desiered look and feel?

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Correct.

    The external presentation of data is handled by the application.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Feb 2004
    Posts
    4
    Ok I now understand.

    I'll put together a simple working booking database, once I know its all working, I'll begin the code for the website which will display any results.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •