7 Database Design Mistakes to Avoid (With Solutions)

Designing a database is an important part of implementing a feature or creating a new application (assuming you need to store data).

There are many different ways data can be stored. There are some limitations put in by the database, but there are a lot of different techniques that can still be used.

This can mean there are some design concepts that are used that I believe should be avoided.

I’ve listed these “database design mistakes” in this video. I’ve explained what they are, why it’s not a good idea to use them, and what the solution should be instead.

Some of them you may disagree with, and some of them you may have seen in live production systems that work well. That’s OK. This video explains the reasons why it’s not usually a good idea and what the alternatives are.

Timestamps:
00:00 Intro
00:25 Mistake 1 - business field as primary key
02:18 Mistake 2 - storing redundant data
03:24 Mistake 3 - spaces or quotes in table names
04:49 Mistake 4 - poor or no referential integrity
06:11 Mistake 5 - multiple pieces of information in a single field
07:30 Mistake 6 - storing optional types of data in different columns
09:04 Mistake 7 - using the wrong data types and sizes

[mariaferguson]

They are all great tips. A good one to remember was Mistake 6. Storing optional types of data in different columns. Thank you for the information!!

[arnotek]

I have been doing database designs for many years - your information is spot on. Thank you for putting this out.

[samfreitag]

I think number one is brilliant! I have seen many changes over my life, like phone numbers getting longer, so it's really an intelligent approach in my opinion. Thank you for another great video!

[houstonvanhoy]

Nicely done! I remember learning these principles years ago when I read Ralph Kimball's books on data warehousing. They still ring true - the books and the principles. ✅

[mostinho]

Done Thanks for making these keep it up!

Mistakes to avoid when designing db:
1. Use a separate field as primary key (not a business relevant field or a field that you already have as an attribute because that might change, or not be unique). You can still enforce constraints on other fields such as making them unique without using them as pk

2. Don’t store redundant data (store date of birth only instead of age and date of birth) and calculate the age from dates

3. Maintain referential integrity by implementing constraints (primary key, foreign key, unique, not null, check)

4. Optional columns should be moved to another table (for example customer phones, home phone, work phone etc) should be moved out of the customer table and into a customer_phone table which references customer id and then stores different phones for each customer with different phone types. Phone typed can have their own table and then the customer_phone can reference the phone_type_id

[nicholassmith]

Very useful info, thanks! Especially the tip about optional columns 👍

[hindy]

This is a very informative and high quality channel! Thank you!

[mosesnandi]

This was amazing. Databases are also amazing and powerful tools!

[samibouakeldev]

Every second of this video is worth it, I learned a lot 🙏🙏

[sql]

Thank you, The video was very informative especially your example on how to store multiple phone numbers.

[lysol]

Awesome video and full of so much useful information. Thanks for sharing this 👏👍

[anonymousstrong]

Thank you so much for this informative presentation. I am a DNP student, totally new to information technology. My professor sent us the link. These videos are making my class whole lot interesting. I do not hate the course anymore. Thanks to your videos.

[helersu]

Thank you for the good information. I can say that information number 6 was very useful for me. 😀

[amlife]

Thank you. That was very informative and useful

[tulntizarak]

Best Channel on YouTube, new subscriber, keep going 😍

[martinnhantran]

Fantastic video, I learnt alot. Thanks

[abiolasamuel]

I'm guilty of step 6; always trying to use columns instead of creating new tables.

[tashi]

Great insights. Thank you. #6 might have few issues, specially when you doing search and now you have join so many tables. I think it comes under EAV pattern, which has many issue like complex queries, performance, limited indexing etc. for me complex query was issue when you try to build a query for search with many filters.

[idk]

can't thank you enough mister .. i really appreciate it 😞❤

[juniorlukusa]

Thanks for sharing all of these mistakes