Previous
Database Relations 
Next
Other Stuff In SQL 
SQL(Structured Query Language) Tutorial
Database Designing

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...

#idnameiqsexepisodes
11Homer Simpson70m1,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...

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...

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...

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 
blog comments powered by Disqus
Subscribe to Feed