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!

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)'.

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

@DatabaseStar

Жыл бұрын

Thanks! Glad it was helpful.

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

@DatabaseStar

Жыл бұрын

Thanks, glad you find it useful!

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

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!

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!

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

@DatabaseStar

Жыл бұрын

Thanks!

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

@DatabaseStar

2 жыл бұрын

Glad you like it!

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!

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

@DatabaseStar

2 жыл бұрын

Thanks! Glad you liked it.

Thank you. That was very informative and useful

@DatabaseStar

2 жыл бұрын

Glad it was helpful!

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

@DatabaseStar

3 ай бұрын

Glad it was helpful!

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

Жыл бұрын

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?

Fantastic video, I learnt alot. Thanks

@DatabaseStar

Жыл бұрын

Glad it was helpful!

Love you for this, thank you.

@DatabaseStar

Жыл бұрын

You're so welcome!

Thanks. these videos are so useful

@DatabaseStar

Жыл бұрын

Glad you like them!

Excellent video. Thank you.

@DatabaseStar

Жыл бұрын

Glad you liked it!

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

@DatabaseStar

Жыл бұрын

You're welcome!

Great video, thanks for making it

@DatabaseStar

Жыл бұрын

Thanks, glad you liked it!

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

7 ай бұрын

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

Thanks for sharing all of these mistakes

@DatabaseStar

3 ай бұрын

No worries!

Best Channel on KZread, new subscriber, keep going 😍

@DatabaseStar

Жыл бұрын

Thanks! Glad you like my channel!

and all tips were equally helpful!

@DatabaseStar

Жыл бұрын

Glad you think so!

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

@DatabaseStar

7 ай бұрын

Yeah it’s a good one to remember!

Very good video! 👏👏👏

@DatabaseStar

Жыл бұрын

Thanks!

Great!

@DatabaseStar

Жыл бұрын

Thanks!

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

5 ай бұрын

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.

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

4 ай бұрын

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.

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

Ай бұрын

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.

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.

I love this channel

@DatabaseStar

Жыл бұрын

Thanks!

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

11 ай бұрын

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.

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.

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

@DatabaseStar

10 ай бұрын

What do you mean by a city guide?

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/

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.

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!

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

thank you

@DatabaseStar

Жыл бұрын

You're welcome

Genius!

@DatabaseStar

5 ай бұрын

Thanks!

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!

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

@DatabaseStar

2 жыл бұрын

Thanks!

good video

@DatabaseStar

Ай бұрын

Thanks!

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.

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!

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.

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

11 ай бұрын

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

8 ай бұрын

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

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

@DatabaseStar

3 ай бұрын

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