Page 1 of 1

Database Normalization - How To Apply To My Own Database?

Posted: Fri Nov 03, 2006 10:37 am
by Superman859
Hello everyone! For starters, I'm new to PHP and MySQL, although I've been studying Java for quite some time (thus am familiar with programming).

I've gone through and learned quite a bit about PHP in the past 1-2 weeks, although I am just beginning to study databases.

I've been reading about Database Normalization - while I understand the main ideas and general concepts, as well as the examples provided when describing it, I'm unsure how to determine whether my database is normalized or if it needs work.

I had hired someone to create the database for me, as I hadn't started studying PHP/MySQL at the time.

About My Database:

The site is a membership site, meaning paying members (either monthly or yearly) can access the content on the site. Part of this involved putting all the pages in the database (or that's how he did it anyways).

He set it up so that I can place all the pages in the database. Each page is added as a new row in a table. The database currently only consists of one table - site_info.

The Current Structure:

There are nine columns - one for each part of the page. (pagename, language,culture,interact,general,news,login,title,body).

language, culture, interact, and general are parts of the nav area. You can view the site at http://www.learn-korean-now.com to get an idea of what each part is for.

Every column except pagename contains HTML. for example, the news section contains the html that goes in the news area on the site. The body contains the html that creates the body of the site (basically everything in between the body tags).

When I create a new page, it automatically retrieves data from the home page and uses the same data to fill in the columns for the new row (for the new page). It uses the new page name for the page name row. I can then go back and edit each individual field (or column) via my admin panel, and adjust as needed. Usually the only adjustment is the body, as the nav area and news area stays the same for most pages.

That's the main layout and how the database is involved with the site. It works for getting pages in the database, but I'm unsure of how efficient the system is. I do not believe there is a primary key. The first column is the pagename, but I don't think it's even set as a primary key. Also, from what I understand keys should generally be numbers, not strings.

I've uploaded two screen shots of the database, as I don't know a better way for showing what I am talking about...The URL's show the browse and structure screens in phpMyAdmin

http://www.learn-korean-now.com/beta/structure.jpg
http://www.learn-korean-now.com/beta/browse.jpg

Besides the key issue, a lot of the same content is repeated in each row (the navarea columns for example - usually the same on all pages). Does this mean I should break them off into individual tables? I'm more confused on 2NF and 3NF than 1NF. I've read over what they say about it in my book, and on wiki, but am still a little confused on how to apply normalization to my own database.

I'd like to get the structure of the database worked out before I start adding all the pages/content. They are already on my computer, but I feel now would be the best time to make any adjustments on the database.

Any help would be very much appreciated!

Posted: Sat Nov 04, 2006 4:25 am
by mcccy005
For starters, yes there is a lot of repeated data.
Presumably the "News" page (for example) would have the same basic look and feel as every other news page on your website. So you could basically design a template in php and html and use php to access only the text for that news page from the database rather than store all the html aswell in each row of the database.
You can also modify the database so that each page has an id to it; then create a new table called images which has an image id; a page id; and a location of the image on the local server (or you could store the actual image in the database itself but this is a bit slower on the database as a whole)

As for all that normalization, I did study it at Uni but it never made much sense to me either!! All that I think is important (for a smaller website that doesnt have thousands of users coming through per hour) is that data isnt repeated.
For example, I have a database with for example holiday boat accommodation whereby boats can be hired:
Each boat has an id; as well as a marina id (plus other stuff)
The Marina table also has an id as well as a region id.
Each region table has a region id and a state that the region is located in.

A basic example of normalization is this - the state of the boat can be found by traversing through boat.marina_id-->marina.region_id-->region.state.
If I stored what state the boat was located in in the state table, the database would not be normalized becuase according to the relationships, the piece of data called state representative of where the boat is would actually be repeated at least twice. Therefore it would not be normalized!

But like I said....as for 2nd - 5th normalization I have no idea sorry!

Posted: Sat Nov 11, 2006 5:20 pm
by califdon
You seem to have a simple, one table database. The only significance of normalization in this case would be to determine if the table should be split into multiple tables in a relational database. I don't see anything to indicate that you need other tables for this application. The key concept is that of an entity; I would think your entity here is a web page.

I agree with the other poster; I would create HTML template pages with embedded PHP to access the specific variable text or images that you need on a page, rather than store all the HTML tags and duplicate so much of the code from one page to another. My impression is that the designer preferred to just dump everything in the table rather than writing the PHP code. But I wouldn't do it that way.

Don