CODEWITHSUNDEEP
c#winformsc#-4.0
Nil Pun
Nil Pun

Reputation: 17373

Saving date and time to database

I'm working on small windows form .net project where we need to build the appointment scheduler. Below is the screen mock of what the UI should look like.

enter image description here

The user should be able to select either AM, PM or OWH irrespective of hours. Or, he can choose specific hours from drop down.

I've a question on what would be the best approach to design the database for this. Initially I was thinking just using the Date and Time however, this would not work as it would be hard to store the AM/PM or OWH slots (without hour portion).

I'm thinking of doing below, any suggestions and best practices will be much appreciated: 1. Create Slots Table which basically contains:

SlotID  | Description
1       | AM
2       | PM
3       | OWH
4       | 00
5       | 02
....

  1. Create Appoinment Table which contains below:

    AppointmentDate | SlotID |......

    04-02-2014 | 1|......

    04-03-2014 | 5 |......

Any other suggestions will be appreciated.

Upvotes: 0

Views: 303

Answers (2)

Brendan Green
Brendan Green

Reputation: 11914

A suggestion is to store the type of time that is being stored.

Have an additional table for TimeType:

TimeTypeId | TimeTypeDescription
1          | AM
2          | PM
3          | OWH
4          | Time

Then change the Appointment table to do:

AppointmentDate | TimeTypeId | TimeOfDay (nullable)
2014-02-04      | 1          | (null)
2014-02-05      | 4          | 7 
2014-02-06      | 4          | 19

You only care about the TimeOfDay if the appointment has the right TimeType.

Upvotes: 1

hmartinezd
hmartinezd

Reputation: 1186

My suggestion, if you want to use it the way you have designed (for whatever reason), I would save it as starttime and endtime, that way for example can be AM = starttime 6 and endtime 18, and if the user selects 15 (for example) you can save it as starttime 15 endtime 16. I guess that way you can keep time ranges, from hour to hour, or larger ranges as AM, PM.

Hope that help you.

Upvotes: 0

Related Questions