Let's start off by talking about what a database is and how it would be efficient in using it. It wasn't until about a year and half ago now that I was first introduced to this new age of Database. I discovered a lot of neat things and even learned about databases the very long way.
A database is exactly how it sounds its data that has a base. The base being the place where you store all your data. If you have ever been to a library perhaps you have used the Index to search for an author, this is a database of authors. Ever seen a rolodex on someone desk, imagine multiple rolodex’s that were huddled together and were joined together by links of information. Simple it sounds but there is a lot more to it than that, but once you learn the simple stuff it makes a hell of a lot more sense. A phone book is a database of phone numbers; stored alphabetically by last name. You can simply look up Griffin Peter, until you find the Griffin Peter you’re looking for. A database is exactly that, you can use it to store information, and instead of you looking it up, you simple query the database to look up the information in the column in your rolodex.
Let’s get some terms and definition’s out of the way. You don’t need to fully understand what the definition mean. Just know that these are term’s used in Data basing is important. You eventually will get the meaning.
C.R.U.D.
Create, Read, Update, and Delete.
Primary Key
The candidate key selected as being most important for identifying a body of information (an entity, object or record).
Source (http://dictionary.reference.com/browse/primary%20key)
Table
A collection of records in a relational database.
Source (http://dictionary.reference.com/browse/table)
Database
One or more large structured sets of persistent data, usually associated with software to update and query the data. A simple database might be a single file containing
many records, each of which contains the same set of fields where each field is a certain fixed width.
Source (http://dictionary.reference.com/browse/database)
Record
An order set of fields usually stored continuously. Sometimes also called a “row” in data basing, and in spreadsheets it’s always called a “row”.
Source (http://dictionary.reference.com/browse/record)
Normalization
In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships
Source (http://webopedia.internet.com/TERM/n/normalization.html)
So let’s just create a small database that will store student’s class schedule. I am not going to go into the steps of creating it in Mysql for this is just a theory lesson and understanding on how to create a proper database.
So if you have a database of student schedules you’re going to have to store certain information. So we need to store Names of all the students, where they live and there courses, what time there courses are, and grade. Now this sounds easy enough, you have to remember that when creating a database you want to have as few records as possible and little redundancy also, preferably none, and absolutely no anomalies. Because you want have your database spend the least amount of time looking for the information, and never have data that can’t C.R.U.D. with out creating an anomaly.
Most databases have complicated ways of looking up information stored in there databases. A Database like Oracle is really efficient because they have written algorithms so that it can spend less time searching and return results faster. A simple Mysql program doesn't have such complicated ways, though it is very fast if you have millions and millions of records it will slow it down. And if you have millions of millions of people constantly asking the computer where this information is it can really task a computer and it will be lagy in response. The algorithms in databases are not the only thing that makes them different.
So let’s create a skeleton for a database where we want to store a student schedule.
Table Name: Class Attendants
AttendantID, First Name, Last Name, Address,
Table Name: Classes
CourseID, Course Name, Teacher, Time, Credits
Table Name: Student Course Schedule
SchedualID, StudentID, CourseID
This is a good database design for a school schedule because it is flexible and can be applied to many different schools if your running any kind of school it can be used. If you considered each table as sort of a rolodex you were looking at, perhaps this would be an easier concept of tables. If you look at the table Class Attendants, you will notice that it contains the Name of the student there last name and there address along with a Primary Key, Attendant. This unique identification (Attendant) allows for multiple students and teachers even if they have the same name, to be stored in the database. Same as the Class Table, along with the Student Course Schedule. The table Student Course Schedule is what we call at junction table, it connects both Class Attendants table and the Classes table together. If we didn’t have a Unique ID for each person in the Class Attendant table we would have to use something else to use another unique system of identifying our Class Attendants from one another. Let’s say our table looks like this.
Table Name: Class Attendants
First Name, Last Name, Address,
Table Name: Classes
CourseID, Course Name, Teacher, Time, Credits
Table Name: Student Course Schedule
SchedualID, First Name & Last Name, CourseID
With out the unique identifier to tell our students apart, when we try to C.R.U.D we would have problems. Such as we want to pull to look at all the classes Peter Griffin is in. We would call on our database to look at First Name & Last Name and pull all records pertaining Peter Griffin and find out that there are 32 classes for Peter Griffins in the school. Since your school only has 8 Classes for each student and there are 32 for this one student there must be an anomaly. Because there actually must be 4 different Peter Griffins going to your school and because you didn’t uniquely identify them apart you have no idea which class this Peter Griffin goes to. We could how ever identify them apart by there address, oh unless your kids come from George Forman’s house. Then you have multiple students with the same name from same house so that wouldn’t work either. So it is important when Data basing to take all these considerations in to play because when you C.R.U.D. your data you don’t want to have anomalies such as 4 Peter Griffins with no course schedule because you can’t tell any of them apart.
So our original skeleton of a database is actually quite fine and will work well. Though I am sure there are some out there that would create it differently, I will attempt to explain why you might create a similar database that works fine also but doesn’t even look close to mine. Creating a database is almost like an art, the creation of your own database will be exactly what is needed by you, there is but one true rule in creating a database. No anomalies, a bad database will have redundant data in it. But redundant data though it slows the system down and is just a crappy database, you cannot have a database that has anomalies, or you won’t be able to C.R.U.D your data and you don’t want that.
For a nice tutorial on database normalization and all the rules that go with it please go to
http://www.rsolutions.net/RSweb/TOC.htm and click on either Database Normalization or No Slide Title.
I know this article is not the greatest but I hope other’s instead of ranting how poor it is would contribute to what I have missed or didn’t make sense of.
Cheers
Lenbot
