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
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.
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.
The current model is somewhat limited. It allows a user to add only one of each product to an order. Why? Because the OrderProduct has a composite primary key that consists of order_id and product_id. Since a primary must always be unique, only one record of a certain order_id - product_id combination can exist in the table.
So how can we allow a user a quantity of more than 1 of the same product in the same order? There are two solutions.
The first is to remove the unique constraint from the OrderProduct table by removing the primay key definition from the table (and possibly adding a surrogate primary key). Then, multiple records with the same order_id / product_id combination would be allowed. This would work, but the second solution would be cleaner: add a quantity field to the current OrderProduct table. The primary key definition would be maintained and the application using this database would update the quantity field when a user alters the quantity of a product in his or her shopping basket.
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 :)
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.