let's take a look at how to properly design a database and cover some of the most important database design principles what makes a good database design well there are two golden rules that we need to follow and if we do the end result will be a database that performs well doesn't take up as much storage won't causes problems down the road and contains high quality data golden rule number 1 is we want to avoid duplicate and redundant information this can cause all kinds of chaos within our database number 1 it increases storage because we're storing
the same date of multiple times number two it reduces data quality because there's a chance that someone will modify that data in one place not in another which will result in inaccuracies here's a good example let's say that we want to track customers their orders and the products that they ordered very very common database in today's world so here we Jam all of that information into a single table and look at the result we have duplicate information here here here and here every single one of these columns contains duplicate information somewhere this is why it's
important during the design phase to properly identify those distinct entities for example here these two columns these are associated with the customer this column is associated with the product and this one is associated with orders so what that tells us here is we should really have three tables rather than a single table so breaking these out into three tables will eliminate all of that duplicate data now we can add our primary keys and foreign keys and set up our relationship links between them so we can still write queries that return this view to our users
and our applications that anyone using this data but under the hood it's only stored once designing your database in such a way will get us half of the way here with the next golden rule which is around data quality ensuring accuracy and completeness of our data another way to accomplish this is using what are known as constraints constraints are things that we place on columns so we have things like default constraints which if a value isn't supplied in an insert statement then a value will automatically be applied the default value that we define in that
constraint a common one there is using the get function in sequel tying it to like an ordered a column so when that record is entered when an order is placed it will automatically fill in the current date another one is a check constraint a check constraint is used to ensure a valid range of values within a column is entered another kind of constraint which we covered in a previous nugget is a foreign key constraint which enforces relationships between multiple tables here's a more complete design of customers products and orders notice that each one of these
tables only contains information about that entity and nothing more and we set up relationships through primary keys and foreign keys between all of these tables so can you identify the primary keys in all of these tables and the foreign keys in all of these tables let's try it primary key for customers table is the customer ID I know the little key icon gives it away there for the products table its Product ID for the orders table its order ID and for the order details table it's actually both of these order ID and Product ID that's
known as a composite primary key because it is the combination of both of these columns now can you identify the foreign keys I see one right here customer ID this is a foreign key because it references a primary key in another table customer ID this ensures that the customer must exist in order to place an order right so we couldn't just put an order in here that didn't have a customer a valid customer that exists in this table associated with it the other one is down here in the order details table this product ID references
the primary key of Product ID in the products table this ensures that orders can only be placed on valid products now let's talk about the design process because a good majority of it is actually just attempting to identify everything that you need to track and how you're gonna structure it in the database actually doing it building it is the easy part step number one and this kind of goes without saying but identify the purpose of your database now when you're learning this stuff you can have fun with it one of my first databases was tracking
all of my music so I identified everything that I wanted to store artists albums tracks and then all the data points within there and that leads us into step number two here which to discover and collect information whatever it is you want to store just gather all the potential data points and then you can organize them by entity into tables I really enjoy this part of the design process and you can actually do this anytime anywhere you can look at a real world object and try to extract all the data points off of it in
fact I do this when I'm bored I did this the other day I was sitting in the doctor's office bored out of my skull because the magazines were like 20 years old and I wasn't gonna read them so I'm like you know what well I'm sitting here I'm gonna turn this room into a database and I just looked around and I noticed pictures lots of pictures on the wall so I'm like there's a table right there a pictures table now what kind of information can I extract from a picture well the height the width the
frame style the artist and then what the picture actually is maybe a description of the picture notes about the picture so all that information could be stored inside of a table in fact there's a fun little exercise you can do this right now look around wherever you are Sarah went on an object and start identifying all the data points that you would want to track in a database about that object this is how database developers think now obviously looking at an object isn't gonna give you everything you need which is why there's always a lot
of research involved in this phase and also talking to other people the next step is to take all that information you gather about all the objects you want to store and transform them into tables then take all of those data points and transform them into columns within those tables from there you'll want to identify your primary keys within those entities to enforce uniqueness within a table and then your foreign keys to set up relationships between those objects the last step in the design process is to normalize and refine your database design now normalization is a
very important database design concept that is just a set of rules and guidelines and if we follow them the end result will be these golden rules right here we will completely eliminate redundant data and increase data quality normalization consists of what are called five normal forms and they all build on each other so the first pass through of your database design you'll attempt to get the first normal form then second normal form then third normal form and that's that's really where most folks go today is just the third normal form that's all you need to
hit these golden rules in the CBT nugget we covered the basics of database design I hope this has been informative for you and I'd like to thank you for viewing
