homepage Welcome to WebmasterWorld Guest from 54.204.107.48
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Best data type for drop down of hours and days
imagined

10+ Year Member



 
Msg#: 4178638 posted 4:00 pm on Jul 29, 2010 (gmt 0)

I need the user to select the minimum appointment lead time, to ensure that we receive adequate notice of new appointments before the client arrives. It can range from 4 hours to 2 days.

So in the drop-down menu I will include:
  • 2 days
  • 1 day
  • 8 hours
  • 4 hours
  • 2 hours
  • 1 hour


What is the best way to store that information on the database? What data type?

Timestamp? Time?

 

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4178638 posted 4:03 pm on Jul 29, 2010 (gmt 0)

I guess it depends on the DB, but assuming MySql or POSTGRES. My vote would be DATETIME.

imagined

10+ Year Member



 
Msg#: 4178638 posted 4:21 pm on Jul 29, 2010 (gmt 0)

I'm using MySQL.

Isn't DATETIME for a specific date and time, for example, 2010-07-29 11:20:45?

I'm sorry for my limited knowledge of databases.

What if somebody selects a 4 hour min lead time? How would it be entered into the database?

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4178638 posted 4:30 pm on Jul 29, 2010 (gmt 0)

How are you going to use the data? Set the lead time before a specific appointment date/time (then use DATETIME) or lead time in general before appointments (then use INT)? Maybe you can give a little more information about what you're doing?

imagined

10+ Year Member



 
Msg#: 4178638 posted 4:38 pm on Jul 29, 2010 (gmt 0)

It's an application for appointments. The admin has to set the minimum appointment time so the user won't schedule the appointment, let's say, 10 minutes before the service is supposed to be worked on.

Let's say the admin sets the minimum lead time as 1 day. That way, users cannot schedule an appointment 8 hours nor 4 hours nor 1 hour nor 10 minutes before. They have to do it at least 1 day before the service is supposed to be executed.

So the admin, on his settings panel can select from a drop-down menu, the minimum lead time. It can range from 1 hour to 2 days. Whatever the admin picks, needs to be stored on the database.

My question is, how would you store that setting into the database? That value is to be checked on by the application everytime someone tries to schedule an appointment.

How would you store the value 2 days in the database? How would you store 4 hours in the database?

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4178638 posted 4:49 pm on Jul 29, 2010 (gmt 0)

Then use INT:
- 1 hour = 1
- 8 hours = 8
- 1 day = 24
etc.
When making an appointment, make sure that the current time is at least that many hours before the scheduled appointment.

imagined

10+ Year Member



 
Msg#: 4178638 posted 4:56 pm on Jul 29, 2010 (gmt 0)

So simple! I'm embarrassed I didn't think of it before! :-S

You know, maybe TINYINT would be more efficient to follow the rule "Choose the smallest type that your data will fit into".

Thank you!

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4178638 posted 5:16 pm on Jul 29, 2010 (gmt 0)

As long as your application will never need to allow more hours than TINYINT will hold (255, I believe). So if someone decides they need more than 10 days in the future, you'd have to change the field to SMALLINT.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved