Exploring Physical Data Models
1. Exploring Physical Data Models
Hello! Welcome to the deep dive into the third and final type of data model, the physical data model. Here is where theory meets practice as we explore how the model is created.2. The role of the physical model
Previously, we used the analogy of building a house, starting with a sketch and progressing to detailed blueprints. Now, it's time to transform those plans into tangible structures. In data terms, this is our physical model, the stage where we craft our entities, attributes, and relationships.3. The physical model's details
Before we establish the relationships between our tables, it is essential to define the data types for each attribute of the entities we have. Let's bring back our e-commerce platform for this. Customers, orders, and products should contain all the attributes defined in the logical model.4. The physical model's details (1)
For the physical model, we have to expand the entities with a more technical detail, adding data types. Data types dictate the kind of data each column will store in the database.5. Establishing primary keys
Now, we're ready to integrate relationship details technically. A fundamental step in building our data model is assigning a primary key, commonly abbreviated as "PK", to each entity. In our model, a customer can have many orders, indicating a one-to-many relationship. Here, customer id will serve as the primary key for the customers table.6. Establishing primary keys (1)
Meanwhile, invoice number is the primary key for the orders table and stock code for the products table. Primary keys help us ensure that each record in a table has a unique identifier that never repeats.7. Creating Primary Keys
In our e-commerce model, we create the products table with a unique stock code as the unique identifier for each item. We achieve this by specifying the stock code with its data type, in this case, VARCHAR(255), as the PRIMARY KEY. This key means each product is listed once but can fill many orders. For example, suppose someone buys a book and three pencils. In that case, the orders table has separate lines for each, all related to our products by stock code. The PRIMARY KEY clause in our table design specifies the stock code as the unique identifier for each product.8. Establishing foreign keys
The next step in the model is to establish connections between related entities using foreign keys, often abbreviated as "FK". Foreign keys are crucial for creating relationships between tables. It creates a bridge between tables using each table's primary keys. For example, in our e-commerce model, each order contains various products. We can reflect this in the orders table by referencing the products table through a foreign key that connects to the stock code primary key in the products table.9. Creating foreign keys
Let's create that foreign key to link the products table with the orders table. Note two changes that must happen when you create the table; First, add a new column that will refer to the key of the foreign table. In this case, the datatype for stock code is VARCHAR(255). The second step is to use the FOREIGN KEY and REFERENCES clause, which defines a column that references the primary key of another table, creating a relational link between the two tables. For the FOREIGN KEY clause, we will use the stock code column created in step one and for the REFERENCES clause, we will use the foreign table named products and its primary key, which is stock code.10. Finalizing the physical data model
We've established data types and relationships in our physical model and are ready for the next steps. Our current model shows updated columns and links.11. Terminology and functions overview
Let's review the essential functions we've learned as we wrap up.12. Let's practice!
Now, let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.