7 Database Design Mistakes to Avoid (With Solutions)

DB structure for a city guide? includes product orders and room booking

About the phone number, you don’t usually care about too many phones about someone, unless you’re designing a database for a phonebook, usually two fields on the customer for phone more than suffice, make your queries simpler(no joins needed) and with a slightly better performance(that explain can tell you over thousands of records), so putting as dismembering the phone number as multiple tables, as a globally good advice you’re potentially adding unneeded complexity.
When you’re modeling a database you need to know the requirements.
A generally true good advice is KISS(Keep it Simple Stupid)

Redudant Data vs. Query Performance

let's say you would have to a bunch of joins in order to retrieve the data. Is storing the value redundantly a solution in this case?

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.

thank you

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. ✅

Fantastic video, I learnt alot. Thanks

Can you LATER change varchar(200) into varchar(300) in your API code and database, but still keeping the stored old variables in database, without wiping them when you make the change in length?

Age can be set as a non-persistent calculated column. Then it's value is populated at run-time. But that's more of a data warehouse thing than 3NF design.

Excellent video. Thank you.

HI, great video, quick question. Why #6 you create 3 tables to solve the problem? why not simply have a phone number table and customer table and one of the columns in the phone table can be the type instead of having a separate table just for the type? isn;t the type table not necessary? I would like to hear your thoughts

Doesn't #6 move you to a snowflake design?

and all tips were equally helpful!

Love you for this, thank you.

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

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

Thanks. these videos are so useful

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

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!

Great!

Which soft ware tool do I use to create a Data Base design

I love this channel

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

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

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!!

Very good video! 👏👏👏

Great video, thanks for making it

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

Thank you. That was very informative and useful

What if you make the primary key as an auto incrementing code number that’s an integer and controlled by another key that’s unique

so number 6 is basically making a proper join table rather than having a messy ass many to many ?

I thought names with spaces had to be in brackets? or is that only alias names that have spaces?

I came for database tidbits... I stayed for the soothing accent =]

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

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

It is very sorry that there us only 4 comments in 13 years fir such a informative video presentation.

Could you please make a video on how someone might do an entry level ETL project?

Or perhaps if you could offer any insight here.

Let us assume that someone "knows" enough SQL and Python to move forward into data engineering, how would this person develop an interesting entry level ETL pipeline project to put on their resume/portfolio?

What are the necessary tools at a fundamental level? I know for batch processing I could use something like Apache Airflow.. but that is about it.

I really want to start an ETL project but there are surprisingly not that many clear resources on that topic as of yet.

Thanks for the videos.

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