SQL Tutorial: Database design

let's learn more about what database design means database design determines how data is logically stored this is crucial because it affects how the database will be queried whether for reading data or updating data there are two important concepts to know when it comes to database design database models and schemas database models are high-level specifications for database structure the relational model which is the most popular is the model used to make relational databases it defines rows as records and columns as attributes it calls for rules such as each row having unique keys there are other models that exist that do not enforce the same rules a schema is a databases blueprint in other words the implementation of the database model it takes the logical structure more granularly by defining the specific tables fields relationships indices and views a database will have schemas must be respected when inserting structured data into a relational database the first step to database design is data modelling this is the abstract design phase where we define a data model for the data to be stored there are three levels to a data model a conceptual data model describes what the database contains such as its entities relationships and attributes a logical data model decides how these entities and relationships map to tables a physical data model looks at how data will be physically stored at the lowest level abstraction these three levels of a data model ensure consistency and provide a plan for implementation and use here is a simplified example of where we want to store songs in this case the entities are songs albums and artists with various pink attributes their relationships are denoted by blue rhombuses here we have a conceptual idea of the data we want to store here is a corresponding schema using the relational model the fastest way to create a schema is to translate the entities into tables but just because it's the easiest doesn't mean it's the best let's look at some other ways this er diagram could be converted for example you could opt to have one table because you don't want to have to run so many joins to get song information or you could add tables for genre and label many songs share these attributes and having one place for them helps with data integrity the biggest difference here is how the tables are determined there are different pros and cons to these three examples I've shown the next chapter on normalization and D normalization will expand on this from the prerequisites you should be familiar with the relational model dimensional modeling is an adaptation of the relational model specifically for data warehouses it's optimized for OLAP type of queries that aim to analyze rather than update to do this it uses the star schema in the next chapter we'll delve into that more as we will see in the next slide the schema of a dimensional model tends to be easy to interpret and extend this is a big plus for analysts working on the warehouse dimensional models are made up of two types of tables fact and dimension tables what the fact table holds is decided by the business use case it contains records of a key metric and this metric change is often fact tables also hold foreign keys to dimension tables dimension tables hold descriptions of specific attributes and these do not change as often so what does that mean let's bring back our example when we're we're in analyzing songs the turquoise table is a fact table called songs it contains foreign keys to purple dimension tables these dimension tables expand on the attributes of a fact table such as the album it's in and the artist who made it the record in fact tables often change as new songs get inserted albums labels artists and genres will be shared by more than one songs hence records and dimension tables won't change as much summing it up to decide the fact table in a dimensional model consider what is being analyzed and how often entities changed let's do some X