Explaining Database Relationships...

Yeah, database relationships are kind of confusing. To illustrate these relationships, we are going to use Microsoft Access, a very powerful DBMS (or Database Management System, which is a program that is used to create, manage, and search databases).

So consider the following scenario (which I've wanted to start, so this is a perfect opportunity): I'm trying create a database of all of the scripture I read that helps with certain things we experience in life. Below is one table with some data that will be included.


This looks like a spreadsheet, no? Well, it's actually a table that is part of a database in Access. If you look at it, there is repeated data. Three out of four books are Psalms, and two of the four topics are about Joy. This data can be normalized, or broken into smaller pieces without repeating data. Take a look at the diagrams below.


I created two more tables. One that references each book, and another that references each topic. Finally, I replaced the text in both of the columns in my first table (Reference) to the number that corresponds to the number in the tables. Because I named the fields in "Reference" BookID and TopicID, I also named them BookID and TopicID in their respective tables. This implies the relationship between the numbers and the text that is in the tables that contain these same fields.

What's so Great About a Relational Database?

Take a look at the spreadsheet below. This is for an actual business selling fashion items like shoes and purses. This spreadsheet is every item in this client's inventory. While it might be a little overwhelming at first, it makes sense. There is a column for the item title, inventory number, price, etc. It also has columns (if you scroll way to the right) like classification, supplier, warehouse, ad template name, posting name, etc. that has a lot of repeated data.





Let's go over the problems with this method of keeping a database.

• There is an incredible amount of data that is repeated. The Classification, Warehouse, Supplier, Ad Template Name, Posting Template Name, and many other fields all have data that is repeated over and over again. This is bad for the following reasons:
  
  1. Repeated information is hard to change easily. What if this client had to change all of the products that said "custom sandals" to "custom summer wear?" She would have to go through a very messy find-and-replace, which would be much harder than it sounds, considering this particular spreadsheet contains over 2000 rows (which I had to cut down to upload). This problem has occurred, and because it is an inconvenience to change everything in the spreadsheet, the client resorts to changing all of the items "from here on." Hopefully we can all see why this is a bad idea. We then have to write programs in order to interpret whether the cell says one thing OR the other, in every situation interpretation occurs. Obviously not a fun time. (This can be changed with a relational database because you only have to change the text in one table, because all of the occurrences in the large table would be just numbers referring to whatever value is in the other table. Change the value in that one place, it changes throughout.)
     
  2. Mistakes are easily made when data input is all text rather than, say, a dropdown. While a dropdown can be created when a program has values to choose from (like a table of values), if it's just one big spreadsheet all input has to be straight text. This is very conducive to errors. Considering this spreadsheet is on her website and is subject to searching, this causes many problems. Let's say she accidentally puts "Custom Booots" instead of "Custom Boots." That means when someone searches for "boots" that won't come up. Also considering that copy and paste is commonly used in data entry, this could make for all of the entries in a session to be left out of a very important search. (This doesn't occur in a relational database because rather than everything being text, you have the options that appear in the table that connects with the integers in your main table.)
• There is very little focus on data type with spreadsheets. For example, if you scroll over to column AM (Attribute13Value), you will see the apparent height of the shoe. Let's say I'm a female that is looking for some shoes, but I absolutely cannot do shoes over 2". I can search for shoes that are 0 to 1/2", I can search for shoes that are 1 3/4" to 2 3/4", but I can't compare values. I can't "sort by shoe height." While in this case it's not that big of a deal considering there are only three categories, comparisons can't be made when all of the data type is text. Data types are simply the classification of what type of data appears in the cell. If all of the information is of the data type text, which is often the case with spreadsheets, when you "order by" a column it puts the numbers in alphabetical order. So, essentially, higher-level queries (a query being essentially the way a DBMS searches a database for information) are much more difficult and often impossible. (This isn't the case in Relational Databases because data types are much more easily assigned in databases and you can essentially do any query you want, like I want shoes that are between the height of 2" and 3.5" and are between the sizes of 8.5 and 9.5, which would never happen if you are just dealing with text.)
• While creating a database can initially make the size much larger, once you get to databases of this size you would save a lot more space with a database rather than a spreadsheet type of database which uses a lot of overhead. Overhead is when you basically say "I don't want to assign the data type of this piece of data, so basically just make sure it has enough room to be whatever it wants." This isn't a good idea. When you know that you are just going to be putting shoe size in a column, there is no need to leave it the data type of "text." This data type takes up a lot of space, because it has to make sure that "just in case the user puts in a lot of data, the room is available," even if the user knows that more than a certain amount will never be used. The increased overhead and ultimately size of the database results in slower search queries, as well as inserting or deleting from the database. (This isn't the case with Relational Databases because typically when you create the database you are very specific with which data type is going to be in that field, and how much space it will need.)
  

Overall, while it might take a little bit of time to go through the normalization procedure, especially with a project like this, it would be well worth it for the advantages of having multiple tables as a Relational Database rather than just one table in a database, which is essentially a spreadsheet.

(While I only took the time to thoroughly explain Relational Databases, there are other types of databases, including flat, hierarchical, and network databases. You can find out more about these types of databases at techFAQ.com.)

What is a Database? Why Do We Use Them?

Before we get into what a database is, we should probably clarify what data are. Data are stored representations of objects and events that have meaning and importance in the user’s environment. Yeah, it’s hard to define concepts like data, and when you try, it ends up sounding so general that it could include anything. Basically, data are any collections of information that we find relevant or valuable. Your name, address, and social security number are data.

Now that we know what data are, how do we organize and collect data? This is the purpose of a database. A database is an organized collection of logically related data. This definition is much more easily graspable. It’s a collection of data that relate to each other. This isn’t as complicated as some people may think. A teacher’s hard copy gradebook is technically a database. It holds the data of the students’ grades and how they calculate into the final grade. A wedding guest book is a database. Folders of receipts could be considered a database. Anything that you use as a reference to look at data collected is considered a database.

So… what makes this type of database different from the database we always hear about? "Our database crashed, but thankfully we had a couple backups." Articles citing "databases of phone numbers" used in tracking crime. Most of the databases we hear about today are computer-based. These databases can be accessed automatically with programs and websites to show information to the user. So while with a hard copy gradebook the teacher would have to open up the book and consult the hard data, typically with computer-based databases another program accesses it and displays it in a nice fashion. For example, facebook has a (pretty extensive) database with all of the users, their information, and the relationship between users. Obviously if we looked at the database we would just see lines of information, but facebook’s programs access the database and serve it up nice and pretty.

What are your options with creating a database, you ask (or even if you didn’t)? Well, one way you can create a database easily is with Microsoft Excel. Many people create databases with excel, putting information like bookkeeping figures, class information, hour logs, inventory, etc. into spreadsheets. This is obviously data, correct? And it’s being collected in one spot that relates it, correct? Therefore, we consider it one type of database. However, there is another type of database that is most commonly thought of when the term is used. A Relational Database, the type of database created in Microsoft Access, establishes the relationships between entities by means of common fields included in a file, called a “relation.” WOAH! We just skipped over a lot of information. Basically the difference between a relational database and the databases we’ve been talking about are the following…

While “traditional” databases simply have columns of information and look more like spreadsheets, relational databases have what we call tables. These tables are basically like mini-spreadsheets. Instead of having one huge spreadsheet with all of the information in it, much of it being repeated, you have many that all relate to each other. So a personal finance table instead of saying "food" three dozen times in the same column and "clothing" two dozen times, it will say "1" and "2" in the respective columns. This table then relates to another table, called... say... "Expenditure Type" that has the code "1" in one column and what it means, "food" in the second column. This same concept is used for every part of data in a table that might be repeated, to eliminate repeated data (this process of eliminating all repeating data is called data normalization). So a different table would be created called "Restaurants," and a code would be given to each, and perhaps a different table would be created for each member of the family who might spend money. While this process may seem to cause more trouble for the creator, a relational database is MUCH more useful than a spreadsheet-like database, and will be much less of a hassle in the long run. (To see why, read the later post, "What's so Great About a Relational Database?")

To conclude, a database is basically a collection of information that you want to store. The kind of databases that are used on computers are typically relational database. A relational database is a database which includes several tables of data and links them together rather than repeating the same data over and over.