DOC PREVIEW
SJSU CMPE 226 - Database Normalization And Design Techniques

This preview shows page 1-2 out of 7 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 7 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 7 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 7 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Database Normalization And Design TechniquesDatabase Normalization And Design TechniquesBy: Barry WiseIntroduction One of the most important factors in dynamic web page development is database definition. If your tables are not set up properly, it can cause you a lot of headaches down the road when you have to perform miraculous SQL calls in your PHP code in order to extract the data you want. By understanding data relationships and the normalization of data, you will be better prepared to begin developing your application in PHP. Whether you work with mySQL or Oracle, you should know the methods of normalizing the table schema in your relational database system. They can help make your PHP code easier to understand, easier to expand upon, and in some cases, actually speed up your application. Basically, the Rules of Normalization are enforced by eliminating redundancy and inconsistent dependency in your table designs. I will explain what that means by examining the five progressive steps to normalization you should be aware of in order to create a functional and efficient database. I'll also detail the types of relationships your data structure can utilize.Zero Form Let's say we want to create a table of user information, and we want to store each users' Name, Company, Company Address, and some personal bookmarks, or urls. You might start by defining a table structure like this: usersname company company_address url1 url2Joe ABC 1 Work Lane abc.com xyz.comJill XYZ 1 Job Street abc.com xyz.comWe would say this table is in Zero Form because none of our rules of normalization have been applied yet.Notice the url1 and url2 fields -- what do we do when our application needs to ask for a third url? Do you want to keep adding columns to your table and hard-coding that form input field into your PHP code? Obviously not, you would want to create a functional system that could grow with new development requirements. Let's look at the rules for the First Normal Form, and then apply them to this table. First Normal Form 1. Eliminate repeating groups in individual tables.2. Create a separate table for each set of related data.3. Identify each set of related data with a primary key.Notice how we're breaking that first rule by repeating the url1 and url2 fields? And what about Rule Three, primary keys? Rule Three basically means we want to put some form of unique, auto-incrementing integer value into every one of our records. Otherwise, what would happen if we had two users named Joe and we wanted to tell them apart? When we apply the rules of the First Normal Form we come up with the following table: usersuserId name company company_address url1 Joe ABC 1 Work Lane abc.com1 Joe ABC 1 Work Lane xyz.com2 Jill XYZ 1 Job Street abc.com2 Jill XYZ 1 Job Street xyz.comNow our table is said to be in the First Normal Form. We've solved the problem of url field limitation, but look at the headache we've now caused ourselves. Every time we input a new record into the users table, we've got to duplicate all that company and user name data. Not only will our database grow much larger than we'd ever want it to, but we could easily begin corrupting our data by misspelling some of that redundant information. Let's apply the rules of Second Normal Form. Second Form 1. Create separate tables for sets of values that apply to multiple records.2. Relate these tables with a foreign key.We break the url values into a separate table so we can add more in the future without having to duplicate data. We'll also want to use our primary key value to relate these fields: usersuserId name company company_address1 Joe ABC 1 Work Lane2 Jill XYZ 1 Job StreeturlsurlId relUserId url1 1 abc.com2 1 xyz.com3 2 abc.com4 2 xyz.comOk, we've created separate tables and the primary key in the users table, userId, is now related to the foreign key in the urls table, relUserId. We're in much better shape. But what happens when we want to add another employee of company ABC? Or 200 employees? Now we've got company names and addresses duplicating themselves all over the place, a situation just rife for introducing errors into our data. So we'll want to look at applying the Third Normal Form. Third Normal Form 1. Eliminate fields that do not depend on the key.Our Company Name and Address have nothing to do with the User Id, so they should have their own Company Id: usersuserId name relCompId1 Joe 12 Jill 2companiescompId company company_address1 ABC 1 Work Lane2 XYZ 1 Job StreeturlsurlId relUserId url1 1 abc.com2 1 xyz.com3 2 abc.com4 2 xyz.comNow we've got the primary key compId in the companies table related to the foreign key in the users table called relCompId, and we can add 200 users while still only inserting the name "ABC" once. Our users and urls tables can grow as large as they want without unnecessary duplication or corruption of data. Most developers will say the Third Normal Form is far enough, and our data schema could easily handle the load of an entire enterprise, and in most cases they would be correct. But look at our url fields - do you notice the duplication of data? This is perfectly acceptable if we are not pre-defining these fields. If the HTML input page which our users are filling out to input this data allows a free-form text input there's nothing we can do about this, and it's just a coincidence that Joe and Jill both input the same bookmarks. But what if it's a drop-down menu which we know only allows those two urls, or maybe 20 or even more. We can take our database schema to the next level, the Fourth Form, one which many developers overlook because it depends on a very specific type of relationship, the many-to-many relationship, which we have not yet encountered in our application. Data Relationships Before we define the Fourth Normal Form, let's look at the three basic data relationships: one-to-one, one-to-many, and many-to-many. Look at the users table in the First Normal Form example above. For a moment let's imagine we put the url fields in a separate table, and every time we input one record intothe users table we would input one row into the urls table. We would then have a one-to-one relationship: each row in the users table would have exactly one corresponding row in the urls table. For the purposes of our application this would neither be useful nor normalized. Now look at the tables in the Second Normal Form example. Our tables allow one userto have many urls associated with his user record.


View Full Document

SJSU CMPE 226 - Database Normalization And Design Techniques

Documents in this Course
SQL-99

SQL-99

71 pages

XML

XML

52 pages

XML

XML

14 pages

Chapter 9

Chapter 9

45 pages

Load more
Download Database Normalization And Design Techniques
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Database Normalization And Design Techniques and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Database Normalization And Design Techniques 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?