MySQL Database Design Best Practices
A column should represent only one dimension/concept/meaning
Why:
- It creates confusion. It makes your code complex and hard to understand.
For example, status column should only represent enable or disable. If you want to represent if it’s formal, you can add a column formal.
Multiple groups of the same structure columns should be put into another separate table
Why:
- It may cause business logic process to become more complex.
- low scalability. It’s hard to add a new type of group of columns and add new columns for each group.
How:
report (id, name, s1_content, s1_status, s1_submit_time, s2_content, s2_status, s2_submit_time,...) |
=>
report (id, name) |
Moving TEXT/BLOB column to a separate table
Why
If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.
MySQL you are discouraged from keeping TEXT data (and BLOB, as written elsewhere) in tables frequently searched.