Use lower case with MySQL. Lower name databases, tables, and columns. This keeps everything simple and you’ll never need to guess whether something is upper case or lower case or a mixed case. Also, on systems that treat “file.db” differently than “File.db”, lower case everything saves a great deal of headaches.

In every single table you have, make your first column “id”. Make it a primary key, auto-incrementing, integer. This keeps things very clean when you need to positively identify records and make changes or deletions. It also works great with the save function and object code as previously mentioned.

When referencing other tables within your system, make their column names “id_[remote_table]”.  When referencing id values from other systems outside of yours, make them “[outside_system]_id”.  This makes it easy to see whether you’re referencing something internally or externally.

August 28th, 2013

Posted In: Basics, Database

Leave a Comment