In this video, you'll learn 7 of the most common database design mistakes and how you
can avoid them.
I've been designing databases and writing about them on this YouTube channel and my
website, Database Star, for years, and in my career for even longer.
I've made plenty of mistakes and have done a lot of research on database design.
There are quite a few mistakes that can be made when designing a database.
Let's look at 7 of the most common mistakes.
The first mistake is using a business-relevant field for a primary key.
When you design a table, one thing you'll need to define is a primary key.
This is a column or a set of columns that identify the record in a unique way.
Many tutorials suggest that you should find a field that's business-relevant, which is
something that is useful to the business or the users.
Some examples of this are an individual tax number, such as a Social Security Number in
the US or a Tax File Number here in Australia.
Another example could be a national company number which is registered with the government.
Or a part number or item number for an eCommerce system that's provided by the vendor.
However, I believe using a business-relevant field, or a field that's useful to the users,
is a mistake.
This is for two reasons.
The first is that the value can change, which should not happen for a primary key.
Someone can change their tax number or company ID and therefore it no longer identifies them.
The second reason is that you often don't have any control over this field.
The format and value of these fields are often determined by an external system or company.
Vendors control the part numbers for their parts, and the Government controls the social
security numbers.
If they change the format, such as adding another digit, or reassign a value, then it
can mess up your database.
The purpose of a primary key is different from the business-relevant field.
So, what's the solution?
Create a new field that is only used for the primary key.
This is called a surrogate key.
You can call this "id" or the table name underscore ID or something.
This means your column can have its primary key value set and it won't change if the other
data changes.
You can still enforce constraints on the other identifying columns, such as the social security
number.
Just don't make it the primary key.
The second mistake when designing a database is storing redundant data.
Redundant data is any data that is unnecessary or data that does not need to be stored.
Here's an example.
Let's say you have a table that stores people, whether it's called person or customer or
individual or something else.
This table stores a person's date of birth.
You want to store a person's age because you need it in your application.
The age is calculated from their date of birth and the current date.
However, if you want to store the age in the table as well, this could cause problems.
The age would need to be recalculated every day based on the current date to ensure the
age value is up to date.
This would introduce extra complexity into the system.
A better way to make use of a person's age is to have the application, or an SQL query,
calculate the age based on the date of birth and the current date.
This way it's calculated whenever it's needed, and there is no need to store it in the database.
This is something to keep in mind for any calculated field or any field that is calculated
based on other fields.
The third mistake is using spaces or quotes in table names.
Most databases expect table names to be made up of all characters and underscores.
They can be written as just a single name, like customerorder.
You can write it using camel case, but some databases store the names in the same case
so it doesn't really make a difference.
You can also use underscores to separate words in table names, like customer_order.
However, it is possible to add spaces to your table names.
You do this by enclosing the table name in double quotes.
You could then create a table name like this: "customer order".
This may seem like a good idea to make your table names more readable.
However, it can make your queries messy and can cause errors.
If you use a space in your table name, it needs to be in quotes, and if the name is
in quotes then it must be referred to in quotes every time you mention it.
So, in your select queries, update queries, insert queries, they all need to mention the
table name in quotes.
This makes the query a little messier.
It can also make it more prone to errors.
If you forget to use quotes on time, then your query won't run.
So, avoid using spaces in table names and avoid referring to them using quotes.
If you create them without quotes then you won't be able to use spaces and won't have
this issue.
Mistake number 4 in this video is having poor or no referential integrity in your database.
Referential integrity means ensuring the data in your database is high quality and meets
the needs of the system.
This is implemented partially in the way you design your tables, but mostly using database
constraints.
There are five types of constraints used in databases: primary key, foreign key, unique,
not null, and check.
If you have specific rules that need to be enforced on your data, then it can be useful
to create these constraints on the columns in your tables.
This will ensure the data is valid and is not missing where it needs to be populated.
Without the constraints, you may have missing values in fields that you expect to be required.
You may have duplicate values in a column that is expected to be unique.
You may also have IDs that refer to records in other tables that don't exist.
You may be thinking, can't you just leave this up to the user interface or the application?
Yes, you can add some kind of validation to applications to ensure that some fields are
mandatory, and other types of checks.
However, adding it to the database will ensure these checks are done for all methods that
data is added, such as via other systems or processes.
It can also enforce data integrity rules like this when the application is updated or changed.
Mistake number 5 is storing multiple pieces of information in a single field.
When we learn about designing databases, we learn that one field should store one piece
of information.
However, sometimes it's not clear what "one piece of information" is.
One common example of this is an address field.
An address is a location of a building or person or business.
If you have a table for a customer, and store their address, it may look something like
this.
We have a single field for address which could be a 1000 character text field to store it.
An address may seem like a single piece of information, however it actually includes
several things: a street number, street name and type, city, state, country, and a zip
or postal code.
It's usually beneficial to store the address as separate fields, for each of these pieces
of information, rather than a single field.
This will allow you to perform validation on the different components of the address,
such as a valid postcode or street type.
It also allows you to filter on different components, such as all addresses in a city,
without having to perform string manipulation.
I once worked on a CRM system that stored addresses like this.
There were separate fields for each piece of information.
It was much easier to work with.
Mistake number 6 is storing different optional types of data in different columns.
One type of design issue I've seen is storing different optional values in different columns
in a table.
A common example of this is phone numbers.
Let's say you have a customer table and you want to store different phone numbers that
they may have, such as a business phone, mobile phone, or home phone.
One way to store this information is to add columns for each type of phone number.
Our customer table would look like this.
However, all of these fields may be optional.
A customer may not have a home phone but would have a business phone.
A customer may only have a mobile phone.
Or they may have none of them.
This would mean that some or all of the fields would be empty.
Also, what would happen if you want to add a new type of phone number?
Perhaps there's a second mobile number, or they have an assistant whose number you want
to store.
If they are stored like this as separate columns, you'll have to add a new column to the table.
A better solution to this is to store the phone numbers and types in separate tables.
You can have a table that stores customer phone numbers, and have a customer ID for
each one.
This way you can relate the phone number to the customer, and they can have as many phone
numbers as needed.
You can also have a phone_type table which would store the type of phone number, such
as mobile, business, home, or assistant.
Each new type would be a new record in this table, and you can link a phone number to
this type.
This will give you the flexibility to store only the phone numbers that you need, as well
as allowing new types in the future
The final mistake I want to mention in this video is using the wrong data types and sizes.
Part of designing a database is determining the data types and the sizes for each column.
Generally, there are three categories of data: number, text, and date.
But within each of those categories are many different data types.
There are a range of number types depending on if you want decimal places or not, and
how large of a number you need.
Data types also depend on which database you're using, as they are not standard across databases.
Choosing the right data type comes down to your knowledge of the data, your experience,
and your knowledge of data types.
Making an appropriate choice will make quite a difference to your database.
Using the right data type can improve the quality of your data, the performance of the
database, and reduce storage.
Date values, for example, could be stored as numbers or as text fields.
However, it's better to choose one of the valid date data types in the database, such
as DATETIME.
This will ensure only valid dates can be stored, that dates can be sorted and filtered correctly,
and use the database's built-in optimisations for this data type.
The same approach can be taken for character types where you often need to specify a maximum
length.
You can store a first name in a VARCHAR field that is 2,000 characters long, but do you
really need a maximum of 2,000?
You could argue that VARCHAR does not add extra characters and is just a maximum, but
having a shorter field may introduce better quality data.
A limit of 200 or 400 may be suitable instead of 2,000 in this example.
So, to work out an appropriate data type, consider what the data is being used for.
Get some sample data if you can.
Look at the possible data types available in your database.
Discuss it with team members if you can.
It is possible to change data types at a later date, but it can be hard.
And that brings us to the end of this video on 7 database design mistakes to avoid.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit databasestar.com.
That’s where I share my best database-related content.
Which tip from this video was the most helpful?
Was it the option of storing different optional fields such as phone numbers in a different
table, or not using a business field as a primary key, or something else?
Thanks for watching.