7 Database Design Mistakes to Avoid (With Solutions)

Подписаться 52 тыс.

Просмотров 64 тыс.

50% 1

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
/// RESOURCES
📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🧭 SQL Roadmap from basics to advanced: www.databasestar.com/sql-road...
✍️ Learn how to design a database: databasestar.mykajabi.com/edd
📧 Contact me here: www.databasestar.com/contact/
📝 Database Design Exercises: bbrumm.gumroad.com/l/hqsxi
📝 SQL Practice Questions: bbrumm.gumroad.com/l/ulekqk
📝 SQL Performance Quick Guide: bbrumm.gumroad.com/l/fcvny
📝 Ultimate SQL How-To Guide: bbrumm.gumroad.com/l/oqggau

Наука

Опубликовано:

1 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 102

@mostinho7 Год назад

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

@DatabaseStar Год назад

Thanks!

@ziad9533 Год назад

You should also add #5, which is about choosing the correct datatype for the data. For example date in 'datetime' instead of text, and name in 'varchar(200)' instead of 'varchar(2000)'.

@mariaferguson2224 Год назад

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

@DatabaseStar Год назад

Glad it was helpful!

@hindy51 Год назад

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

@DatabaseStar Год назад

Thanks, glad you find it useful!

@nicholassmith6412 Год назад

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

@DatabaseStar Год назад

Thanks! Glad it was helpful.

@arnotek Год назад

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

@DatabaseStar Год назад

Glad it was helpful!

@samfreitag729 Год назад

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!

@DatabaseStar Год назад

Thanks for the comment, glad you liked the video!

@houstonvanhoy2198 Год назад

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

@DatabaseStar Год назад

Thanks! I’ve read one of those books and yeah it’s still quite valid

@sql3051 2 года назад

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

@DatabaseStar 2 года назад

Thanks, I’m glad you liked it!

@mosesnandi 2 года назад

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

@DatabaseStar 2 года назад

Thanks! Glad you liked it.

@samibouakeldev Год назад

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

@DatabaseStar Год назад

Thanks!

@lysol64 2 года назад

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

@DatabaseStar 2 года назад

Glad you like it!

@helersu 2 месяца назад

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

@DatabaseStar 2 месяца назад

Glad it was helpful!

@tashi7160 5 месяцев назад

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.

@DatabaseStar 5 месяцев назад

Good points! I'm glad you like the video.

@amlife180 Год назад

Thank you. That was very informative and useful

@DatabaseStar Год назад

Glad it was helpful!

@anonymousstrong1768 11 месяцев назад

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.

@DatabaseStar 11 месяцев назад

Thanks for the comment and glad you like the video! Good to hear you don't hate the course anymore! Do you have any other questions or issues on databases, that I could make videos about?

@abiolasamuel8092 6 месяцев назад

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

@DatabaseStar 6 месяцев назад

Yeah it’s a good one to remember!

@juniorlukusa9650 2 месяца назад

Thanks for sharing all of these mistakes

@DatabaseStar Месяц назад

No worries!

@tulntizarak6069 Год назад

Best Channel on RU-vid, new subscriber, keep going 😍

@DatabaseStar Год назад

Thanks! Glad you like my channel!

@martinnhantran Год назад

Fantastic video, I learnt alot. Thanks

@DatabaseStar Год назад

Glad it was helpful!

@dennistucker1153 Год назад

Excellent video. Thank you.

@DatabaseStar Год назад

Glad you liked it!

@idk5598 Год назад

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

@DatabaseStar Год назад

You're welcome!

@HossinAzmoud Год назад

Thanks. these videos are so useful

@DatabaseStar Год назад

Glad you like them!

@mannycalavera121 Год назад

Great video, thanks for making it

@DatabaseStar Год назад

Thanks, glad you liked it!

@appstuff6565 Год назад

Love you for this, thank you.

@DatabaseStar Год назад

You're so welcome!

@deyvidwilliam Год назад

Very good video! 👏👏👏

@DatabaseStar Год назад

Thanks!

@appstuff6565 Год назад

and all tips were equally helpful!

@DatabaseStar Год назад

Glad you think so!

@fernandoherranz4095 4 месяца назад

Great video thank you! As for storing phone numbers, you make good points but I don't want to worry about several tables storing all these different numbers. I would likely design it so either a customer/client table has 4-5 options for phone numbers and accept the possible blanks where they show up, or make 1 phone number table with 6+ options for numbers and a notes field for any additional instructions. If you're scaling up and dealing with thousands of phone numbers (like within a large company or org) then your method would likely be better.

@DatabaseStar 4 месяца назад

Thanks! That's a good point, and it's good you've made a conscious decision about your preferred approach and are happy with the impacts.

@Raph0ne 23 дня назад

Thanks for your Wonderful Video of sharing Expertise! As a newbie I'm planning a database but not sure how to reflect the age problem (#2) in Design.. bonus question: how to achieve it in Diagram that for example I take a variable from a config File to do my query? Like if I say calculate the Due date that always is constant (but based on Config Variable x=2 Years) How to draw the diagram for this Matter? Thank you

@DatabaseStar 20 дней назад

Glad it was helpful! I think it depends on if you have the config value in your database or in a separate file. If it's in a separate file, you can read this value when you perform the Update or Insert statement. If it's in the database, you would also have to consider that when you Insert or Update the value. I think the design would be the same - your Due Date would be in the table for your record.

@rafacancode Год назад

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

@DatabaseStar Год назад

Good question! You could do it in 2 tables: one for customer, and one for phone number. You could have a type column in the phone number table that would accept the values you want, such as "Mobile", "Work", "Home", "Work Mobile". However, if there is a defined list, the only way to ensure that the right values can be selected here would be to add a Check Constraint to the column, and then if you're designing an application to edit this data, you would need to define the list in the user interface. If it's a defined list like this, I usually suggest creating a separate table with the possible values (phone_type in this example). This way you can use the FK relationship to ensure the values are only in the list, and can edit the list easily.

@Hacking-Kitten 10 месяцев назад

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?

@DatabaseStar 10 месяцев назад

Yeah that is an option. But having joins isn't necessarily a performance issue. If the tables are small, and if indexes are added, then the query can still perform well.

@AjaniTea 3 месяца назад

At 9:00, how do we handle the case where the customer may have multiple phone types and we want to store all of them? From the diagram shown, I’m under the impression that the customer table contains their customer_id, which allows us to look up the phone_number and phone_type_id in the customer_phone_number table. But imagine we want to add a business_number as well, doesn’t “phone_type_id” make it so that there is only 1 type of number associated with that customer_id? Or are we saying the primary key is the phone number and the customer id is a foreign key? Some clarification here would help. Thanks!

@DatabaseStar 3 месяца назад

The customer_phone_number table will allow you to have multiple rows for a single customer, so you can store a phone number for the "business" phone_type_id as well as the "personal" phone_type_id. You could also have two records for the same customer and same phone_type_id, which means two business phone numbers for the same customer. There's no primary key on this design, and no unique constraint on the columns. You could (and I probably should have) add a primary key column to this table, something like customer_phone_number_id, but this won't prevent a customer having two phone numbers of the same type. If you want to prevent that, you can add a unique constraint (or a PK) on the (customer_id, phone_type_id) columns.

@bushido5206 2 года назад

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.

@DatabaseStar 2 года назад

Good question. I haven't looked into ETL projects but I can do a video on that soon.

@markoviitanen4441 Год назад

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?

@DatabaseStar Год назад

Yes, you can do this. You can run an Alter Table statement to increase the size of a column and the data should be preserved.

@superboba2007 Год назад

I love this channel

@DatabaseStar Год назад

Thanks!

@mariemartin3071 Год назад

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

@DatabaseStar Год назад

I use LucidChart but you can use many different tools. I've got a list on my post here: www.databasestar.com/data-modeling-tools/

@iordanistsapanidis4021 9 месяцев назад

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

@DatabaseStar 9 месяцев назад

What do you mean by a city guide?

@FlutterMapp Год назад

Great!

@DatabaseStar Год назад

Thanks!

@CitAllHearItAll Год назад

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.

@DatabaseStar Год назад

Oh that’s good to know!

@naranyala_dev 11 месяцев назад

thank you

@DatabaseStar 11 месяцев назад

You're welcome

@chadgregory9037 2 года назад

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

@DatabaseStar 2 года назад

Yeah names with spaces need to be in brackets or single quotes, off the top of my head it may depend on the database, but the concept is the same.

@chadgregory9037 2 года назад

@@DatabaseStar oh ok, I'm still new. Thanks for the reply!

@carlosfuentes5538 4 месяца назад

Genius!

@DatabaseStar 4 месяца назад

Thanks!

@abdiag603 2 года назад

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

@DatabaseStar 2 года назад

Making the primary key an auto generating number is a good idea and pretty common. What do you mean by controlled by another?

@abdiag603 2 года назад

@@DatabaseStar example. For Invoice Table you set a default primary key of invoice_id that is autoincrementing and then set an invoice_code that is also autoincremented as the primary key is autoincremented. Id 1 invoice_code 125001 Id 2 invoice_code 125002 ....e.tc

@chadgregory9037 2 года назад

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

@DatabaseStar 2 года назад

Thanks!

@user-sn4nb3ei8p 21 день назад

good video

@DatabaseStar 21 день назад

Thanks!

@zintuplet Год назад

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

@DatabaseStar Год назад

Yes, it would be a more snowflake design or data warehouse design. This would work for that, but maybe not for a transactional database.

@chadgregory9037 2 года назад

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

@DatabaseStar 2 года назад

Yeah, that's right!

@AdvocateChamber 2 года назад

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

@DatabaseStar 2 года назад

Glad you like it! But the video is not 13 years old, it’s only a few weeks old.

@Lara-xr8eo 2 месяца назад

Im curious! What kind of accent/dialect is this?

@DatabaseStar 2 месяца назад

I’m from Australia so it’s an Australian accent!

@rpeetz 10 месяцев назад

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)

@DatabaseStar 10 месяцев назад

That's a good point. However the reason to have a separate phone number is because you can often have multiple phone numbers for a person, and they can have different types (mobile, work mobile, work, home, business, etc). It's not a good design to just have phone1 and phone2 because you don't know what type they are and you're restricted to 2 phoen numbers. Having a lookup table for phone number shouldn't slow down performance, as it won't be a large table, and with indexes the lookups will be quick.

@brdrnda3805 6 месяцев назад

@@DatabaseStar I once worked for a company how did a CRM for pharma sales reps and was following in the "keep it simple" path with several fields. You'll end up with four or five fields for phone numbers and still run into situations where it's not enough. They finally re-did the phone number in a more normalized way and things got just simpler.