Database Normalization - How To Apply To My Own Database?
Posted: Fri Nov 03, 2006 10:37 am
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!
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!