Relational Database Design

A database is a collection of data. Related data are grouped together in a tabular format so that it makes sense. For instance, salary data would better completement work related data than address data.
A relational database is acollection of tables of such data which can be linked with some common culumns, primary key and foreign key in database term. For instance:

EmpId	FName	LName
1	John	Q
2	Mary	James
3	Henry	Dew

EmpId	Type	Desc
1	Primary	This is primary description
1	Secondary	This is secondary description
1	Tertiary	This is tertiary description
3	Primary	This is primary description

Here, these two tables can be connected with the common field EmpId. Also notice that employee 2 does not has any entry in second table. This way information can be separated without loosing its integrity. There would have been duplication and few blanks if these two tables were combined. This is a form of a relational database.

In above, the first table could be called Employee and the second, Description. In database term it would be written as:

Employee (EmpId, FName, LName)

Description (EmpId, Type, Desc)

Employee and Description are called "entity" and those in parenthesis are called attributes.

As with every design, one needs to decide what needs to be put into the table as shown above. How detailed do you want to be will decide on how many tables and how to group them.


Once this has been decided, linking those tables with common field would produce ERD and the documentation something like (for first table above):

Table	Field	Data Type	Length	Description	Constraints
Employee	EmpId	Integer	Unique number that identifies an employee	Primary Key, Identity
	FName	varchar 50	first name of an employee
	LName	varchar 50	last name of an employee

Would be data dictionary where one defines data: name, type, length etc.