How to design a good database?

Watch the video below:

Important Database Terms to Know

• Access organizes information into tables
• Each row is called a record
• A record contains unique information
• Each column is called a field
• A field is a single item of information

What is Good Database Design?

• Divides information into subject-based tables to reduce duplication
• Ensures correctness and completeness of information
• Accommodates data processing and reporting needs
• Provides Access with information it requires to join the information in the tables together as needed

The Design Process

• Determine the purpose of the database
• Collect the information required
• Organize the information
• Divide the information into tables
• Convert information items into columns
• Specify primary keys to uniquely identify each record

Ask yourself: What is the Purpose of my Database? A computer institute may need answers to the following requirements.

• Keep a record of all students
• Performance of students
• Keep a record of all faculty
• Performance of faculty
• Which course was taken most?
• Earnings by course
• Total earnings
• Locality of maximum students
• Effectiveness of advertising

Process of Information Collection

• We can start with existing information. We might have the data in index cards, paper forms, journal, an Excel sheet or a Word document.
• Gather all information to record in the database, such as student first name, last name, address, email, mobile number, course opted for, fees paid, mode of payment, etc
• We can optimize this list later

Sample Data Items for Students Database

First Name:  Last Name:  Address:

Course opted for : MS Access Web Designing MS Excel

Start Date :     Date Completion :  Course Fees :

Payment: Cash Cheque 

Name of Bank :  Bank Branch :   Cheque No.  Date :

Questionnaire: How did you get to know about us?

• Reference (Word of mouth)
• Flier
• Ad in local magazine
• Website

How to organize the collected Data

• Break each piece of information into its smallest useful parts
• For example, break the name into two parts — First Name and Last Name
• Think about the questions you might want the database to answer
• For instance, which was the most sought-after course last month?

Divide Data into Tables

• We divide our data into major subjects like
• Student details
• Course details
• Employee details
• Payment details
• Bank Details

How to turn data items into columns

The following tables can have the following columns:

First Name

Last Name

Address

City

Pin Code

Mobile

WordPress

Excel VBA

Databases

C++

Java

C

Website

Ad in Newspaper

Flier

Word of mouth

Do’s & Don’ts while creating tables

• Do not store the result of calculations in tables
• Access can perform the calculations when you need it
• For example, we may wish to calculate the payment due from a student
• Store information in its smallest logical parts
• For example, create separate fields for Locality, City & Pin code instead of a single line of address

What is a primary key and how to specify the Primary Key

• The primary key is a column or field that uniquely identifies each row
• Always choose a primary key whose value will not change
• If we cannot use a column or set of columns as primary key, we can use the AutoNumber data type
• Fact-less identifiers like AutoNumber are ideal for use as a primary key because they do not change
• For the students’ database, we can use an AutoNumber column as primary key: StudentID for the students table, CourseID for the courses table, EmployeeID for the Employees table

The Design Process

• Set up table relationships to bring the information together in meaningful manner
• Refine the design
• Apply normalization rules to check if tables are structured correctly

Creating Table Relationships

• Access is a relational database management system
• In a relational database, we divide our information into separate, subject-based tables
• We then create relationships to bring the information together as needed using the primary key

Creating a one-to-many Relationship

• To represent a one-to-many relationship in our database design, take the primary key on the “one” side of the relationship and add it as an additional column to the table on the “many” side of the relationship as shown below
• For example, we add the CustomerID column from the Customers table to the Orders table
• Access uses the CustomerID number in the Customers table to locate the correct customer for each order in the Orders Table
• The CustomerID column in the Orders table is called a foreign key
• A foreign key is another table’s primary key. The CustomerID column in the Orders table is a foreign key because it is also the primary key in the Customer table

Creating a many-to-many Relationship

• Consider the relationship between the Products table and Orders table as shown below
• A single order can include more than one product
• A single product can appear on many orders
• Therefore, for each record in the Orders table, there can be many records in the Products table. Also for each record in the Products table, there can be many records in the Orders table
• This type of relationship is called a many-to-many relationship
• To detect a many-to-many relationship between our tables, it is important that we consider both sides of the relationship

Creating the JUNCTION Table

• We create a third table called a junction table
• It breaks down the many-to-many relationship into two one-to-many relationships
• We insert the primary key from each of the two tables into the third table
• The third table records each occurrence of the relationship

What the Junction Table Achieves

• The Order Details table’s primary key consists of two fields — the foreign keys from the Orders and the Products tables
• Together, the two fields always produce a unique value for each record
• The Orders and the Products tables are not related to each other directly
• They are related indirectly through the Order Details table
• The Orders table and Order Details table have a one-to-many relationship
• The Products table and Order Details table have a one-to-many relationship
• From the Order Details table, you can determine all the products on an order
• We can also determine all the orders for a product
• Duplication of data (redundant data) is avoided

Creating a one-to-one Relationship

A one-to-one relationship exists between the Employee Table and the Department table as shown below:

Summarizing Table Relationships

• Determining the relationships between tables helps ensure that we have the right tables and columns
• When a one-to-one or one-to-many relationship exists, the tables need to share a common column or columns
• When a many-to-many relationship exists, a third junction table is created to represent the relationship

3 Important Normalization Rules

First Normalization Rule

• Data normalization rules check if our tables are structured correctly
• First Rule states that at every row and column intersection in the table, there exists a single value, and never a list of values
• For example, you cannot have a field named Email in which you place more than one address
• Think of each intersection of rows and columns as a cell
• Each cell can hold only one value

Second Normalization Rule

• Each non-key column must be fully dependent on the entire primary key, not on just part of the key. This rule applies when you have a primary key that consists of more than one column (composite key)
• For example, suppose you have a table containing the following columns, where Student ID and Course ID which form the primary key: StudentID (primary key), CourseID (primary key) and Course Name
• This design violates second rule, because Course Name is dependent on CourseID, but not on StudentID
• It is not dependent on the entire primary key. You must remove Course Name from the table. It belongs in a different table (Courses)

Third Normalization Rule

• Each non-key column must be dependent on the primary key and nothing but the primary key. For example, suppose you have a table containing the following columns: CourseID (primary key), CourseName, Fees, Discount
• Assume that Discount depends on the fees
• This table violates third normal form because a non-key column, Discount, depends on another non-key column, Fees
• Column independence means that you should be able to change any non-key column without affecting any other column
• If you change a value in the Fees field, the Discount will change accordingly, thus violating this rule
• In this case Discount should be moved to another table or created as a calculated field

Further Reading:

Database Design Basics