Once developers find themselves in the MySQL world, they will almost certainly hear some advice on how they should go about designing database schemas in MySQL. This blog post will provide insight into what you should consider when dealing with database schemas in MySQL.

Preface

Designing a MySQL database schema is an inevitable part of the career of every MySQL database administrator or even a developer. Developers and MySQL database administrators usually turn to the schema design side of MySQL to improve query performance, normalize their databases, add or drop certain indexes, modify columns, also for other things.

Generally, what one database administrator considers to be a “good” database design might not seem so perfect for another DBA. Everyone has their own set of preferences — depending on experience and other things — but in general, even while MySQL does move forward at a rapid pace, with improvements being made left and right, MySQL schema design practices do not change much.

We are going to start from some of the basic things that you personally may already know but might not be as readily apparent to other developers.

The Basics of MySQL Schema Design

Some of the general advice regarding MySQL schema design sounds something like this:

1. Make sure MySQL databases only store data that is needed.
2. Choose optimal data types for certain use cases.
3. If we store variable character values in columns, we use VARCHAR. If we store text, we should use TINYTEXT, TEXT, MEDIUMTEXT , or LONGTEXT. If we find ourselves using integers, we should probably look into TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT depending on a specific use case.
4. We should give our data types a specific length — for example, we should avoid using VARCHAR(255) when VARCHAR(100) would suffice, etc. By doing so, we enable MySQL to reduce data size.
5. If we have a lot of data, we should use indexes to improve performance, but we must also keep in mind that we should avoid using redundant and (or) duplicate indexes.

The general consensus among DBAs is that MySQL schema design should be based around a few core points:

1. Define the requirements for the database.
2. Gather, organize and refine the data. If needed, add indexes to improve performance.
3. Create relationships among tables.
4. Refine and normalize the database design.

#schema #mysql #database