Introductory Database Theory: Systems And Design - The relational model
(Page 3 of 6 )
As databases advanced, so did common standards. The process of updating information in a database filled with repeating data eats terrible amounts of time. Consider the following example. We have a database of congressional laws, represented by Law 1, Law 2, etc. Along with the law's name, we will list the congressman who initially introduced the law, their phone number and their e-mail address. Here's what the table might look like:
Personally, I'd fire John Smith for his obvious lack of work, but that's beyond the point. Here, we have a basic table. You can see that two congressmen (Sam Jenkens and Adam Schroeder) initiated more than one law. This is a very simplistic table, and initially you may not see anything wrong with it.
Let's say a congressman moves houses and has his phone number changed. Instead of changing one record, three or four must be changed. In a corporate environment with thousands of records, this would take far too much time to complete, and the system's resources would take a major hit because of it. What if one especially eager congressman initiated five hundred new laws? Five hundred rows must be updated. This is just not practical.
The relational database model gives us an alternative by creating a link between two different tables. We can snatch the congressmen information and put it into a separate table and relate it to the laws table. The table shown above would work much better if it were designed like this:
This enables us to relate the two tables to each other by a primary key; in this case it's the Con_ID field of the first table. A primary key is simply a field that is used to set a relational yet unique reference to any record in a table. Since the second table also has a field that matches the primary key, we refer to it as the foreign Key.
Notice that the congressmen in the first table relate to which laws they initiated in the second table via the primary key, Con_ID? Now, we could setup an application that would link the two tables together and give us the same results we would have received from the first non-relational example model. What this also enables us to do is update each congressmen's phone numbers in one place and one place only, requiring much less system processing. On a much larger corporate scale, you can see how this type of database would save processing resources.
This is a very basic look at relational databases. For the purposes of this article, you should just remember that relational databases enable more than one table to be related (or connected) to each other for far superior organization and effectiveness when querying the database.
Next: Metadata and indexes >>
More MySQL Articles
More By Steve Adcock
/ 26 
