A guide to relational database design
Relational databases match data using common attributes. The resultant organisation is much easier for a user to understand and manipulate than a random data set. For this reason, relational databases are the predominant data storage method for medical, financial, and logistical institutions among others. Below is an introduction to relational databases and how to design them.
What is a relational database?
A relational database is simply a database model that stores information in tables. The tables take the form of rows and columns. Any and all manipulation of the data will either be done on the tables themselves or will generate a new table to describe a new relationship.Each row on a table will contain similar information in each column.
A sales database, for example, may contain a table of customers and contact information, that will have a column with the different customer's company names, a column with their respective phone numbers, another with their email addresses, and a final column with their account balances.
Manipulating a Table
The two basic operations that can be performed on the table are isolating a subset of rows or a subset of columns.
Using the above example, one could either separate all the accounts with incomplete information (rows) or all the accounts with a negative balance (columns).
Manipulating several Tables
The other option is to perform an operation between two or more tables.
Again using the sales contacts table, let us assume we want to combine it with a table containing each company's mailing address.
The database will then create a new table with the extra column which contains the mailing address, compare the company name columns for matching information and add the correct addresses to the applicable row.
The command syntax used to manipulate the information will vary based on the program being used, but the operations themselves are all very similar in concept.
Isolating a piece of a Table
What if the mailing address for one of the above companies changes? You would simply instruct the database to update the information in the address column and in the applicable company's row.
In conclusion
Due to the fact that relational databases inherently contain so much flexibility, there are several ways within the basic parameters that they can be designed.
The most important consideration when beginning a database is the long term sustainability of the system, in spite of growth and changing information.
Remember that while each individual manipulation seems simple, after 10,000 manipulations your data may be unrecognisably distorted if your starting model was flawed.