Database Designing

Database Designing is arguably the most important part of a project. Unfortunately, it cannot be taught. The only way to learn how to design is by design databases by yourself. You will make mistakes - but after a dozen or so databases, you would get the hang of it. I'll try to pass on the lesson I learned from designing databases and some huge database books.

The most important part in designing a database is the relationships part. You have to know which relationship to use. Hopefully the database relationship page has given you an idea of how to use them.

There are some other concepts that you need to keep in mind when designing the databases. I'll try my best to put those in layman terms.

Atomicity

I know this word have some official sounding definition - but to me this means 'store only one value in one field'. Take for example the Character/Episode relationship in our Simpsons database. There was another way to do that. I could have created a field in the table with all the episodes as a comma separated list. Something like this...

#	id	name	iq	sex	episodes
1	1	Homer Simpson	70	m	1,2,3,4

The 'episodes' field has a list of all the episodes that Homer has appeared in. This would eliminate the need to create another table and maintain a many to many relation. But I choose the other way(ie, the reference table). That is because of the principle of 'Atomicity'. Here, the episodes field have multiple values in a single field. And atomicity specifies that there should be only one value in a field. That is because there are many, many disadvantages to the method I just described...

You will not be able to join tables using the field.
Searching will become harder.
It becomes harder to maintain over time.
Deleting an episode ID from the field will require application code(like PHP code)
And a lot more.

So when designing a table make sure that there is only a single value in a field.

Redundancy

Redundancy means duplication of data. It is something we try to avoid. To explain this further, take a look at the 'Quote' table...

SELECT * FROM Quote LIMIT 0,5

Here, the character_id field has the ID of the character. Instead, I could have just put the name of the Character right there. Something like this...

SELECT Quote.id, Character.name AS character_name, Quote.quote FROM Quote INNER JOIN Character ON Quote.character_id=Character.id LIMIT 0,5

I did not do that because it would have caused redundancy - the duplication of data. In this case, the name of the Character will show up many times. This is bad because of the following reasons...

Database will become bigger. More space will be required to store it.
Editing the data becomes harder. For example if Homer changes his name to 'Max Power' or 'Homer Thomson', we will have to make the change in many different places. And if it is not done properly, there will be data integrity problems.
Joining multiple tables is possible - but not optimal. You can join the Character and Quote table on the 'character_name' field - but its better to do it using a primary key - like 'Character.id' and its foreign relationship - 'Quote.character_id'.
And more...

Normalization

Normalization is a process of analyzing a relation schema/database design to make sure it satisfies a certain normal form. There are many normal forms...

First normal form
Second normal form
Third normal form
Boyce-Codd normal form
Fourth normal form
Fifth normal form
Domain/key normal form
Sixth normal form

It is impossible to explain these without resorting to hardcore database jargon. And I have no desire to put you through that. For now, I will give you the link to the Normalization article in Wikipedia. Read that if you feel brave.

More Information on Database Designing

Previous
Database Relations

Next
Other Stuff In SQL

Contents