Is it a good idea to use string data type for dates in MySQL instead of using datetime data type? - Stack Overflow
Date Data Type Question
Reddit’s database has only two tables
Alright, let's correct some things here.
First, as many have pointed out, the blog post is quoting an article from 2010 - and that article is paraphrasing a presentation Steve gave. I'd recommend at least looking at the rest of the 2010 article - it gives some context for the use of postgres as a key-value store rather than just a relational store.
Video presentation starting at the schema discussion
Next, we've got more than just two tables. The quote/paraphrase doesn't make it clear, but we've got two tables per thing. That means Accounts have an "account_thing" and an "account_data" table, Subreddits have a "subreddit_thing" and "subreddit_data" table, etc.
EDIT: To add as a final point, the context of the video is "Steve's lessons from building reddit." These are lessons about bootstrapping a startup; you don't necessarily have the time or funds to hire a DBA or to have a perfect DB; and running a data migration when you're NOT a DBA but rather, just trying to get new features out there and working so you can become profitable is not necessarily the best use of your engineering time. You just need something that works for your needs, as you grow. And yes, that means you have to be aware of the shortcomings of your data store as you grow, and be prepared to do something "better" in time - for some applications, that means, well, hiring a DBA and doing it right. For reddit, it meant caching the hell out of everything.
More on reddit.comwhat is the reason there is no boolean data type in mysqli bind_param() ?
Videos
Always use the column type for what what is needed; if you are using a date, use DATETIME, if it is a timestamp, use TIMESTAMP and so on.
Depending on in what you are coding, all the formatting of the data can be done on the actual page in whatever language you are using.
Also, you can take advantage of MySQL functions such as NOW(), rather than using the language's version and then storing it into the database.
If your data is of date type then store the data in a DATE (or DATETIME if you have a time element to it).
If you store dates as strings then you are asking for trouble! For example, what is to stop somebody writing a value of 'I am not a date' into you string 'date' field? Or what happens if you have '20111019' and '2011-10-19' and want them to be treated as equal? Furthermore you will be missing out on a whole raft of MySQL DATE and TIME specific functions
Never store a date as a string if you can possibly avoid it.