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. In this example I will show you a model containing just the customer, order and product entities.
Once you have indentified the entities you want to model you can analyze the relationships that exist between the entitities.
- Between order and product there is 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 tables (Order and Product) and a junction table (OrderProduct), as shown in the database model below. Note how orders and products 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.
Order quantity
If you are wondering whether you could add for example an order_quantity 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. A query that finds the number of products in an order could be easily created with the Strucured Query Language (SQL).
Payment type
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).
Total order amount
Another field you might (and perhaps should) add to the order table is the total_amount of the order, that is, the total price. I can hear you think... that is derived data! You can sum the amounts of the individual products, right? Yes. And no. The price of a product is subject to change. So when you determine the total price of an order by summing the prices of all products contained in the order and the shop keeper doubles the price of one of the products in the order than the price of all previous orders containing that product would change. That is why it is a good idea to calculate the total price at the moment the order is placed and store it with the order.
Storing a price history for products
Another solution to this total price issue would be to keep a history of the price of a product. In that case, you could look at the order date and time and query the price_history table for the price of the product at the time the order was placed. In this case you would not have to store the total price in the order table. My guess is that most systems do store the total price of an order and don't keep a price history though. But you are the designer, so you decide what is best for your system :)
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 or by an SQL query. That is why I don't store the product price inc 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.