5 Mistakes When Creating The Database

Database, It is the backbone of every project. We have to design it very carefully. Even if there is a mistake from the programmer, we will always get the right data. When we plan for the project, Firstly we identify what are the inputs that we have taken from the user then we make the table accordingly. We should keep the following things about the input:

1. What will be the type of input (i.e Number, Text, Decimal, and Timestamp, etc.)
2. The input can be null or not?
3. It will be unique?
4. What will be the minimum and maximum value?

After finding the answer from the above things, we plan for database, tables, and columns. But the mistakes that we make usually, let’s understand together.

Photo by NeONBRAND on Unsplash

Hello Everyone, Database is a very interesting topic for me. I worked with Oracle, MySQL, and SQLite. So as per my experience, I am writing the mistakes that occur when creating the database. It will help you when you have a lot of data. Let’ start:

Mistake 1: Indexing: It makes the column faster while searching. So make indexing of those columns who use the condition for the maximum time. You don’t need to make the indexing of the primary key column, It already indexed.

Let’s understand with the below example: (We have a user table)

-> id (primary key)
-> name
-> mobile
-> dob
-> active
-> created

In this table, we search with mobile maximum times. So we make it indexing from the below command. (MySQL)

create index user_mobile_index on user(mobile);

Notice: Remember indexing has pros and cons. so don’t make the unnecessary column as indexing.

Mistake 2: DataType: It is a very important point to notice because it tells the system for memory allocation and you can find the data with proper condition.

Example 1: We have to store the current time and we make the datatype as varchar(50). And we have to identify the records that come in the last 5 minutes, so we cannot use the timestamp function with varchar type. we have to make it timestamp

Example 2: We have to store the mobile number and we know the maximum length of this column so we make varchar(10). and we know that the user table will lot of records to comes so make the id datatype is bigint rather than int .

Mistake 3: Naming Convention: It identifies the purpose of the column, So the column name should be meaningful. We can make it small but the format should be correct. The basic things that should be remembered are:

a. It is not in the camelcase. (Wrong: userAddress) (Right: user_address).

b. References should be correct. (i.e. user address table has user id. It should be user_id)

c. Boolean database type use is as prefix. (i.e. blocked, active should be is_blocked, is_active)

Mistake 4: null Default Value: Every column that has no other constraint should have a default value. The default value should not be blank because it blocks the memory we should make it as null. The difference between both is to identify with the below queries.

with blank default value:

select count(*) from user where mobileno = '';

with null default value:

select count(*) from user where mobileno is null;

Mistake 5: Set CharSet: Charset is the format for storing the data in the database. Sometimes we tried to insert other languages but the data stored in a non-readable format. So we have to set the right charset for the table based on the requirement.

For example, we know that the user can write in the Hindi language also so we set the charset UTF-16 in the user table.

That’s it. I hope it will help you to make the database perfect. If you like this story then give me a second and clap for me.

You can learn MySQL Commands using this link.

Thank you for reading.