Opinions About Database Design
A good database design is crucial to the success of even the most simple application. Bad designs, like bad code, can kill or stunt the growth of a good product.
So then, a few lessons learned while working with databases:
- Never use DATETIME or similar database-specific functions unless you want to marry your system to a specific database. Which you don’t. Instead, generate the date yourself, in the most robust format you can, and handle that date in your code as you wish. In PHP, you can generate a nice date like this:
$niceDate = date("YmdHis");
- Always use Unique ID’s for every row, even for simple things that don’t seem like they need Unique ID’s. Without a Unique ID, you cannot effectively tie your table to another table in the database, which you’ll probably want to do one day soon. In PHP, you can generate a nice UUID like this:
$uniqueID = md5( uniqid(rand(), 1) );
- Don’t use CAPITALIZE keywords in your SQL statements. Everyone does this, it is standard, but it’s 2003 and most database developers can identify a keyword without your help.
- Always define primary keys.
- Purchase this book.
- Make your SQL as pretty and readable as possible. A good SQL statment should be easy to follow, and people should be able to get the gist of what you’re doing just by looking at your code, without having to review the data itself. Use lots of spaces (not tabs). Whitespace is your friend. An example:
create table example ( exampleID varchar(50) not null, firstName varchar(255), lastName varchar(255), timeStamp varchar(50), primary key (exampleID) )
So there you go.