Database Design

Examples: Sales

Transformation of System : Office -> Excel -> SQL-Server.

Tables:

Product Table -> ProductNo(int,pk) / Description (nvarchar(50) / UnitPrice money / Quantity int (it will be moved to ReceiptProduct table due to many-many relationship constraint).

Customer Table -> CustomerNo(int,pk) / Name nvarchar(50) / Address nvarchar(100) / Town nvarchar(50).

Receipt Table -> ReceiptNo(int, pk) / ProductNo(int)/CustomerNo(int) / Date datetime.

ReceiptProduct (Bridge Table) ->  ProductNo / ReceiptNo / Quantity.

The Normalization is the key concept to the database design.

Split the table into multiple tables to match the real world entity

The main theme of normalization was to use more than one table, with each table representing a single type of real-world thing. This meant recognising that receipts, customers, and product items were fundamentally different things in a sales process, and each should be stored in its own table.

 

The idea of breaking up one large table into several to reduce duplication is called normalization.

Identification of Rows 

There should be one key to identify the rows that's the purpose we have the primary key or unique key.

Dependencies on Keys

The Important part of table design is to ensure that each column in a table states a single fact about a primary key. So in a product table it contains fields like ProductNo(pk), Description, UnitPrice,Quantity.  Here Description and UnitPrice depends upon the primary key ProductNo. but the quantity depends upon the Both ProductNo. and ReceiptNo. in the Receipt Table. So why cant we have the ReceiptNo(Receipt table) in the Product Table. The reason is  because then the other columns (Description and UnitPrice) would depend on just part of the new key. It was essential that for each column in a table to depend on a key, the whole key, and nothing but the key.

Many-Many Relationship

The task of linking the Receipts and Products tables. The link between these tables was going to be a bit different because each product could appear on many different receipts, and each receipt could contain many different products. This, she said, was known as a many-to-many relationship.

It can be thought of  adding foreign keys to each table to represent this kind of link, and it made his head hurt almost immediately. Each table would need to hold a variable number of foreign keys in each row - which is not all we wanted.

Alice said that to model this relationship correctly, she would need to add another table to the design. It is called as Bridge Table, and it would sit between the Products and Receipts tables.

The bridge tables were necessary whenever two tables had a many-to-many logical relationship. Because the new table modelled the link between the Receipts and Products tables, its primary key would be a combination of the primary keys of the related tables (called as compound key).

Enforce one-to-many relationships using foreign keys and constraints.

Model many-to-many relationships using bridge tables. 

Resource: http://www.sqlservercentral.com/articles/Database+Design/72054/