SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a few things relating to mySQL...

    1. True / False Fields

    What is the best way of implementing a field that can either be true or false? Should I go with a char(1) set to 1 or 0 or is there an even better option?

    2. Timestamp Fields

    I want to record an exact date and time (to the nearest second) in a database field. Should I use an int(10) and store the unix timestamp, or use the mySQL timestamp field which stores stuff in a weird YYYYMMDDHHSSMM format (or something like that)?

    Does anyone know a good resource on the web with advice on which field types to use in mySQL? The mySQL manual isn't that helpful on this. I'm pretty confident on relational database design but I want to know a bit more about standard / efficient field types.

    Cheers,

    Skunk

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Allentown PA
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. I wouldn't use a string based field -- I'd use an integer based one which would mean tinyint, smallint, int, etc or an enum field (as they are handled as integers internally)

    2. If you're going to do processing with PHP's date() function, I'd use a unix timestamp as that's date()'s native format. Otherwise you'd have to run the field through MySQL's UNIX_TIMESTAMP() function. I don't think it's a big deal either way, though.

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) I've just been reading the section on "column" types in the manual http://www.mysql.com/doc/C/o/Column_types.html . It seams that if you want to be a complete Scrouge about minimising storage requirements - there is a way to emulate a 1 bit boolean data type,

    VARCHAR(0) NULL

    That is, a VARCHAR field of zero length *not* set to NOT NULL (null values allowed). The two possible values are NULL or "" Which you could translate to boolean by testing for NULL or NOT NULL on the field. There is also a php function is_null() that will do this too. NULL evaluates to false in PHP anyway.

    Otherwise a column of type TINYINT or VARCHAR(1) if set to NOT NULL will only require 1 byte.

    2) I think that it is easier to use the MySQL TIMESTAMP type to store timestamps - hmm, makes sence when you think about it If you need to try to pinpoint a timestamp as close to a user event as possible then maybe you should capture this in your program rather than letting MySQL set it by default - what is your business rule if their is a database error for example - do you still need the timestamp value? MySQL has functions for converting the value of a TIMESTAMP field to UNIX_TIMESTAMP and vice-versa. So it is the most flexible way to store a timestamp IMHO.

  4. #4
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the opinions. I'm not too keen on the "null" vs "" true/false field as BOTH of those would evaluate as false within PHP if pulled "as is" from the database. You can use a function to check if it's null or not and return false or true, but that's extra hassle

  5. #5
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Newcastle, UK
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well I just use a straight int(11) field for datestamps and just whack the unix timestamp, makes life a lot easier processing it later, and as for true/false I'd roll with Ed and go for a 0/1 limited int field

  6. #6
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    /me fwallops Godin with a tree

    How'd you find these forums? Good thing you did, it's lonely being the only wireplayer on here.

    I'm probably gonna go with the int(11) thing, but it's not ideal as it means you can't use mySQL's bulit in date functions (like setting the date to NOW()). There are actually some mySQL functions that can take a standard unix timestamp and convert it into a mySQL timestamp for storage in the database, but using those functions all over the place is too much hassle really when I can just use a standard timestamp in an int.

  7. #7
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wouldnt it make more sense to use a "date" field, that way you can perform comparisons against the date directly in your query.

    I don't know databases much, so this is just a thought.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  8. #8
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Newcastle, UK
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    heh, u rabbited on about em enuff finally stopped by.

    But you can manipulate the timestamps easily enough in script so I dont really think its that much of an issue, also leaves things a bit more portable to other systems should you ever go insane and change the backend data store

  9. #9
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    creole - that's my problem... mySQL has some incredibly powerful date functions but if I'm not going to use them I may as well go for the standard unix timestamp (which is a lot easier to manipulate in PHP).


Bookmarks

Posting Permissions

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