Increasing Columns in a Database Entity
Designing Database Tables – Part 15
Division 2
Forward: In this part of the series, we see how to increase the number of columns in a table entity.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
The Entities
The entities are:
Products(Products, Categories, Price)
Sales(Sales, Date, Employee, Customers)
Suppliers(Suppliers, City, Country)
Customers(Customers, City, country)
SaleDetails(Sales, Products)
Orders(Orders, Date, Employee, Suppliers)
OrderDetails(Orders, Products)
Employee(Employee)
These tables are in at least the 3NF. So they are alright, but there is still a problem. Let us look at the Products table. The word, “Products” as a column name is vague. There should actually be two columns in this position, which are ProductID and Description (to describe the product). You can allow the word, “Categories” as it is or change it to singular, if that will sound better. So far as business is concerned, the word, “Price” is vague. A product has a cost price and a selling price. So the Price column should be replaced by two columns, which are the CostPrice and the SellingPrice. This table is still missing a column. We need to know the number of each product that is in stock. So we need the additional column, Number.
In the Sales table, the sales column, which indicates the act of selling, should actually be saleID. Date as a column is alright. Employee as a column should actually be employeeID to identify the employee who made the sales. Customers should be CustomerID.
In the Suppliers table, the column, Suppliers, indicating the particular supplier should actually be SupplierID. The rest of the columns in the table should give the complete address of the supplier not just city and country. You should have as columns of the table, SupplierID, Supplier Name, Address, City, State, Country and Phone Number. I will put these in a better way below.
Let us look at the SaleDetails table: The column Sales indicate the act of selling, it should actually be SaleID. The column Products, identifies the product, it should actually be ProductID.
Let us look at the Employee table. There is only one column in this table, which is Employee. From a business point of view, this word is vague. This table should have information about employees. So it should have the following columns: EmployeeID, Employee Address, Employee City, State, Country and Phone Number.
This is step 4 of our design procedure. With the above tables, which are already normalized, you cannot really fill values in some of the columns because they are vague. For such a column, replace them with one or more columns that have precise names, so that the information conveyed by the vague column becomes complete. When in doubt as to which column to put, see the workers of the company who requested the database. At this stage you can bring in the computed value columns, but know that their data should be computed when the user of the database needs them. So the above tables are better written as:
Products(ProductID, Category, Number, CostPrice SellingPrice)
Sales(SaleID, Date, EmployeeID, CustomerID, Total)
Suppliers(SupplierID, Address, City, State, Country, Phone)
Customers(CustomerID, Address, City, State, Country, Phone)
SaleDetails(SaleID, ProductID, SoldPrice)
Orders(OrderID, Date, EmployeeID, SupplierID, Total)
OrderDetails(OrderID, ProductID, BoughtPrice, Discount)
Employee(EmployeeID, Address, City, State, Country, Phone)
You can have some of the table names or column names in the singular or plural form depending on how it sounds good. The tables are now very OK.
Note that I have included the column SoldPrice in the SaleDetails table. This is not obligatory. If you are sure that the selling price in the products table will remain the same for all seasons and all years, then if you know the productID you already know the selling price. However, you selling price may actually depend on the season of the year and other factors like promotion of your company. In this case you should put the actual price of the product sold, in the SaleDetails table.
For a similar reason, I have included the actual price bought for a product in the OrderDetails table. When the company buys a product from a manufacturer, the manufacturer can give the company some discount. The discount is not constant in amount or available all the time; that is why I have put the Discount column in the OrderDetails table instead of the Products table. These three columns I have added are not obligatory. However, you should add them as I have done depending on the factors that affect your business.
That is it for this part of the series; we continue in the next part with step 5 of the design process.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT