MySQL Schema Best Practices

1. Use UNSIGNED INT (or BIGINT if needed) for primary key, since you don’t need negative primary keys.
2. Don’t use ON DELETE CASCADE, since you might delete rows you wanted to keep.  Explicitly delete rows in your code.
3. Don’t use ON UPDATE CASCADE, since you might want to change the ID in the parent table of the row being referenced, without changing the ID of the child row.
4. Always use foreign keys to constrain your data and require that foreign keys reference rows that actually exist.
5. Use UNIQUE KEYS where necessary and where they conceptually make sense.  All fields in the UNIQUE KEY must be NOT NULL for the key to be effective.
6. Use singular table names, unless a row represents a plural object.  (A table of chairs would be called “chair”, unless a row represented a set of “chairs”).  This makes code generation easier (esp. for tables named “category”, “specialty”, “country”, etc)

Update:

Keep data types as small as possible for what you need
• Don’t use BIGINT unless you have to! Waste of space.
• Especially true on indexed columns
• Fundamental principle: The smaller your data types, the more records will fit into the index blocks. The more records fit in each block, the fewer reads are needed to find your records.

Source: Jay Pipes, MySQL Presentation, Index Coding Optimization

• Smaller is better – use the smallest data type necessary.  When in doubt, use the smallest one you don’t think you’ll exceed.
• Avoid NULL if possible – Nullable columns make indexes, stats, and value comparisons more complicated.
• Use simpler data types when possible – use integers for IP addresses
• For storage and computation, INT(1) and INT(20) are equivalent.  The numeric is for display purposes only for applications such as the command line client.
• Use FLOAT over DECIMAL for speed – the CPU performs the computations natively, as opposed to the server making the DECIMAL calculation

Source: O’Reilly High Performance MySQL