Things you should keep in mind while designing database schema for your application.

So let’s talk about designing a database schema, there are actually two major problems beginners mostly do while designing.

1. The first and very common problem is not dividing the table into multiple table, instead using one common table to have lots of data. Which actually cause few problems since our table gonna have huge amount of data, that could actually make data read/write operations to pretty slow because MySql generally does linear search if data is not indexed. It also make the management of data little harder because we have made it complete mess xP
2. The second again very common problem is diving the schema into so many tables which could actually make your queries little faster but not in every case because we’re actually having lots of table which mean have to apply lots of joins while retrieving data, that could actually make your queries slower and little bit complex too. Other problem is having management issue because of so many tables and little bit of wastage of storage too.

There is nothing right and wrong about design it’s all about good design so we ‘ll discuss few tips and tricks to design a good database schema.

Let’s decide how many table your database should roughly have!

There is very simple approach to roughly decide, how many tables you should have is to total number of unique entity your application gonna have. Number of tables could be more than that too while normalizing the database schema, we’ll discuss further in the post how to do that too, and number of tables could be less than the unique number of entities too, so you guys are thinking how, right?

so let’s take a simple example to understand this scenario, suppose you have entities like employee, supervisor or manager. Here we have two entities but there is no need to make the separate tables for both the entities because both are the employee to a company with just different privileges so we can just have a self relation to represent both the entities with just one table.

So the thumb rule for roughly deciding number of tables is

## of table = ## of unique entities your application have

if you aren’t familiar with the basics of MySql then I would recommend to have a look at this Basics of MySql

When to divide the table into multiple tables and why?

let’s discuss the theoretical approach first, wait theoretical approach could little bit tedious to understand so let’s discuss the practical approach instead which is easy to understand too.

• Whenever you feel that you’re gonna have redundant values in any of your column that directly indicates that your design is not good enough and you should separate that column from the current table so that it won’t have redundant values.

—** why**: because this leads to few problems like updating a common value having multiple records, suppose you have records something like this

#optimization #schema #mysql #database #database-design