The database design process
A well-structured database:
- Saves disk space by eliminating redundant data.
- Maintains data accuracy and integrity.
- Provides access to the data in useful ways.
Designing an efficient, useful database is a matter of following the proper process, including these phases:
- Requirements analysis, or identifying the purpose of your database
- Organizing data into tables
- Specifying primary keys and analyzing relationships
- Normalizing to standardize the tables
Let’s take a closer look at each step. Note that this guide deals with Edgar Codd’s relational database model as written in SQL (rather than the hierarchical, network, or object data models). To learn more about database models, read our guide here.
Requirements analysis: identifying the purpose of the database
Understanding the purpose of your database will inform your choices throughout the design process. Make sure you consider the database from every perspective. For instance, if you were making a database for a public library, you’d want to consider the ways in which both patrons and librarians would need to access the data.
Here are some ways to gather information before creating the database:
- Interview the people who will use it
- Analyze business forms, such as invoices, timesheets, surveys
- Comb through any existing data systems (including physical and digital files)
Database structure: the building blocks of a database
The next step is to lay out a visual representation of your database. To do that, you need to understand exactly how relational databases are structured.
Within a database, related data are grouped into tables, each of which consists of rows (also called tuples) and columns, like a spreadsheet.
To convert your lists of data into tables, start by creating a table for each type of entity, such as products, sales, customers, and orders. Here’s an example:
Each row of a table is called a record. Records include data about something or someone, such as a particular customer. By contrast, columns (also known as fields or attributes) contain a single type of information that appears in each record, such as the addresses of all the customers listed in the table.
| First Name | Last Name | Age | ZIP Code |
|---|---|---|---|
| Roger | Williams | 43 | 34760 |
| Jerrica | Jorgensen | 32 | 97453 |
| Samantha | Hopkins | 56 | 64829 |
To keep the data consistent from one record to the next, assign the appropriate data type to each column. Common data types include:
- CHAR – a specific length of text
- VARCHAR – text of variable lengths
- TEXT – large amounts of text
- INT – positive or negative whole number
- FLOAT, DOUBLE – can also store floating point numbers
- BLOB – binary data
Some database management systems also offer the Autonumber data type, which automatically generates a unique number in each row.
