6.3: Database Design

Last updated
Save as PDF

Page ID: 14254

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

A properly designed database will divide data into tables to eliminate duplicate information (also known as data redundancy). Redundancy occurs when data is repeated several times in a database. Redundant data is bad, because it wastes space and increases the likelihood of errors and inconsistencies. A good database prevents redundant data when, for example, customer data (ID, Name, Address, etc) is stored in one table and then other tables store just the customer ID to refer to the customers. Another important design consideration to maintain is data accuracy. A common motto when performing data entry is “garbage in, garbage out”. The correctness and completeness of information is important if the user wants to rely on their data. If your database contains incorrect information, any reports that pull information from the database will also contain incorrect information. As a result, any decisions you make that are based on those reports will then be misinformed.

Creating a new database involves determining the database schema, which is a description of the data and the organization of the data into tables. Designing a schema consists of the following steps:

Determine the purpose of the database and which data to include in the database. It is a good idea to write down the purpose of the database on paper — its purpose, how you expect to use it, and who will use it.
Divide information into related groups to create tables and then give each table a descriptive name. Data should be grouped to ensure there is little to no redundancy. Each table must have data related to at least one other table through a table relationship.
Describe the fields and determine the primary key for each table.