- 1Introduction
- 2Database history
- 3Relational database characteristics
- 4Customer contact system
- 5Tables and the primary key
- 6Linking tables
- 7The one-to-many relationship
- 8The many-to-many relationship
- 9The one-to-one relationship
- 10Database normalization
- 11The first normal form (1NF)
- 12The second normal form (2NF)
- 13The third normal form (3NF)
- 14The final database design
- 15Another example: webshop database
- 16Conclusion
Database design tutorial
Another example: webshop database
I hope you are now familiar with database design concepts and that you are able to design a simple relational database. In the database design example below I will recapitulate the tasks and considerations that you will normally encounter when desiging a relational database.
Webshop system summary
In order to get a good view of the data that is involved in the the concept of a web shop let's summarize some of the tasks a webshop normally performs. A webshop
- displays products
- categorizes products
- registers clients
- stores products in a shopping kart
- displays kart contents
- sends orders to the webshop owner
- etc.
Identifying entities and relations
From the list of tasks we can derive the entities that play a role in the web shop system. Products, categories, clients and orders are entities found in nearly every web shop database. Kart contents is an intersting exception. The contents of a shopping card is often stored in what is called a "session", a temporary piece of server memory that is associated with a web user. Some web shops store kart contents in a database table though. It is a matter of preference. In this example I will show you a model containing the customer, order and product entities.
Once you have indetified the entities you want to model you can analyze the relationships that exist between the entitities.
- Between order and product there exists a many-to-many relationship. Each order contains 1 or more products and each product can be associated with 0, 1 or more orders. A many-to-many relationship is modelled with three tables. Two source table (Order, Product) and an association table (OrderProduct), as shown in the database model below. Note how orders and product each have a one to many relationship with the association table. Together they form the many-to-many relationship between orders and products.
- Customers and orders are linked in a one to many relationship. Each Customer record can be associated with multiple Order records and conversely, each Order record is associated with one Customer record.
The tables shown in the model above serve as a simple example. A real Customer table should of course contain more customer data (adress, city, etc).
Below are some general remarks about this data model.
Order table
Each Order row is linked to a unique Customer row with the customer_id foreign key field. The Order table contains only non-key data that is dependent on the order_id (primary key), such as date and time.
If you are wondering whether you could add for example an order_quantity field, or a total_amount field to the Order table, the answer is NO! That data can be derived from data that is already present in the database. The total number of products in an order (order_quantity) can be derived from the OrderProduct table. The total money amount for an order can be derived simply by summing the prices of the products in the order. Such data queries can be easily created with the Strucured Query Language (SQL). A field you could add to the Order table is payment_type. That is unique to an order and can't be derived from other data. (Note that payment_type would become a foreign key field in the Order table referring to a separate table containing payment types).
Product table
In the Product table the product price ex VAT is stored. The price inc VAT can be calculated from the ex VAT value by program code. That is why I don't store the product price ex VAT. You should be aware that storing a product price in this way can still have implications in the future. In this model the product price is stored in a single field. Once you change the price of a product, the old price is gone. If you want to be able to draw historical sales reports from your database you should actually store a separate price history for each product. If a product changed price twice in a certain year, you need a price history if you want to be able to calculate the total amount of money you made of a product in that year.