Determining the Primary Keys in the Database Entities
Designing Database Tables – Part 16
Division 2
Forward: In this part of the series we see how to determine a primary key for an 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.
Primary Key
A primary key is a column whose values uniquely identify each row in the table. The word, “Unique”, means that there are no two values in the column that have the same value. Each value is different in the column. So, after creating the normalized tables, you go on to identity such a column in the table as the primary key.
If you cannot see such a single column, then you have to look for multiple columns whose combined values will uniquely identify each row in the table (entity). Assume that you have seen two of such columns, for each row in the table, the combination of the cell values from the two columns should not repeat itself anywhere again in the two columns. The values can repeat, but not the combination. Each of those combinations, uniquely identify a row in the table. Such multiple columns are called a composite primary key.
If for some reason you are not satisfied with what you think should be a key, and you are sure that the table is normalized, then create a new column and put unique values in the column. You can put numbers such as 1, 2, 3, 4, 5, etc or 10, 20, 30, 40, 50, etc.
By convention, the primary key or primary composite key is the first column (or columns), on the left of the table.
In step 5 of the design process, you determine the primary key for each table. The tables for the example are:
Products(ProductID, Category, Number, CostPrice SellingPrice)
Sales(SaleID, Date, EmployeeID, CustomerID)
Suppliers(SupplierID, Address, City, State, Country, Phone)
Customers(CustomerID, Address, City, State, Country, Phone)
SaleDetails(SaleID, ProductID, SoldPrice)
Orders(OrderID, Date, EmployeeID, SupplierID)
OrderDetails(OrderID, ProductID, BoughtPrice, Discount)
Employee(EmployeeID, Address, City, State, Country, Phone)
In the Products table, the primary key is ProductID. In the Sales table, the primary key is SaleID. Here, EmployeeID is not a key; it will be a foreign key (see later). In the Suppliers table, SupplierID is the primary key. In the Customers table, CustomerID is the primary key.
In the SaleDetails table, you have a composite key made up of SaleID and ProductID. In the Orders table, OrderID is the primary key and EmployeeID here will be a foreign key (see later). In the OrderDetails table, you have a composite key, which are OrderID and ProductID.
In the Employee table, the primary key is EmployeeID. Here the EmployeeID column uniquely identifies all the rows in the table. It is a primary key here; in the Sales and Orders table, it is a foreign key (see later).
The Primary Key Values
What kind of value should go for our keys? For simplicity, just put counting numbers for the keys. So the first row for the product table will have the value, 1 for ProductID in its first row, 2 for ProductID in its second row, 3 for ProductID in its third row, and so on. The first row for the Sales table will have the value, 1 for SaleID in its first row, 2 for SaleID in its second row, 3 for SaleID in its third row, and so on. The values for the composite primary key of the saleDetails table will be read from the Products and Sales table. Apply the same reasoning to the other tables.
That is it for this part of the series; we continue in the next part with step 6, which is the last step in the table creation process.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT