A question about database normalization

badgoat

Hi!

I have been reading a bit about database normalization, and I see some areas in my own projects which could use some work. My question for those who are more familiar with it, is how far do I take normalization? Do I have a separate table each for phone numbers, cities, states, zip codes etc? In my case, I have multiple tables which have entries for the above, so would it be mroe correct to have a table for each and then reference the corresponding entry in the separate tables for the information?

My project won't be getting thousands of entries per week so the way I have it now is fine, but I want to understand the concept correctly and apply it if at all possible.

Thanks!

EDIT

I forgot to add that I have multiple tables in my db which have entries for city, state, zip, phone, and cell. Would it be the correct way to normalize my tables, to split them up so that city, state, zip, and phone each have a separate table?

laserlight

Read PHPBuilder's own article on Database Normalization And Design Techniques.

Generally, you dont need to go beyond 3rd or 4th normal form.

badgoat

Thank you laser.. My head is spinning now, lol. But I am even mroe convinced that my db needs a ton of work. For a table which has 70-something variables which is going to be broken up into many tables, is there a ay to keep them all separate without forgetting any data?

spriensr

You shouldn't have multiple entry places for addresses and stuff like that. Instead it should be on one table but if you are going to have multiple addresses for one user then you should have a users table that references an adress table that can look up the multiple addresses. Hope that helps

Ravenous

Simple.. Say you know you only want to store two phone numbers per person. Since you have a known, finite value of phone numbers you want to store, they can go into the same table as other contact information. However, if you have an unknown number of phone numbers to store, put them in their own table and associate with the users table. For instance:

CREATE TABLE foo (
     id INT(5) NOT NULL AUTO_INCREMENT,
     name VARCHAR(64) NOT NULL
     PRIMARY KEY(id)
) TYPE = INNODB;

CREATE TABLE bar (
     user INT(5) NOT NULL,
     phone_number INT(10) NOT NULL,
     PRIMARY KEY(user,phone_number),
     FOREIGN KEY(user) REFERENCES foo(id)
) TYPE = INNODB;

If you're using MySQL, be sure to specify that the tables are of type InnoDB or BDB as they support foreign keys whilst MyISAM does not.
Database design can be a bitch sometimes. Especially when you get into the hundreds and thousands of tables.

badgoat

Thanks for the tip! I am looking thru PHPMYADMIN and I don't see any allowances for foreign keys.. Is there a limitation of this ?

Ravenous

phpMyAdmin doesn't have the functionality to work with foreign keys. Why that is, I have no idea. But I don't fault the developers for that. It is an open source project. So whatever. What you need to do is learn some basic SQL and write your own scripts to create your tables and such.

Referencing my example above, notice how I have TYPE = INNODB at the end of each table creation? InnoDB is a database engine that MySQL can use. Others are ISAM, MyISAM, BDB, et al. InnoDB supports foreign keys while MyISAM (the MySQL default) does not. InnoDB is also "transaction safe," but lets save that topic for later.

So how to go about creating foreign keys you ask. Well, go ahead and use phpMyAdmin to generate the general table. When you create the table, change the table type from "Default" (that'll give ya' MyISAM) to "InnoDB."

Note: If recreating all your tables doesn't sound fun, you can also change a table's type in the following way:

ALTER TABLE yourTable TYPE = INNODB;

Once the table has been created, click the SQL link and then input the following:

ALTER TABLE yourTable ADD FOREIGN KEY(yourColumn) REFERENCES someOtherTable(someOtherColumn);

It sucks having to do it by hand, but once you get comfortable with the actual language SQL, you may actually prefer writing your own scripts. At least I've gotten there. 🙂

One last thing.. Converting to InnoDB from MyISAM isn't a way of normalizing your database. I use InnoDB simply because it is better at guaranteeing data integrity. InnoDB databases will also require more disk space. So make sure you've got enough. Here's a good primer on InnoDB versus MyISAM.

So yeah.. I hope you understood all that plus my original post. Good luck. Any more questions, just ask.

badgoat

Hi Ravenous,

I learned the basics of MySQL and only switched to PHPMyAdmin for the ease of exporting my tables. I have no problem at all going back to command line, no problem at all 🙂