Other Stuff In SQL
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.
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...
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 means duplication of data. It is something we try to avoid. To explain this further, take a look at the 'Quote' table...
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...
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 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
- Demystified: Database Normalization
- Ten Common Database Design Mistakes
- Project a database: define relationships-entities model (tables, attributes, and relationships)
- Database Design and Modeling Fundamentals
- Project a database: create tables and relationships with SQL
- Project a database: how to use PHP and SQL to create tables and relationships
- Table and Database Operations in PHP
- Practical database design
- Relational Database Design
- A correct approach to define relationships between database's tables
- An Introduction to Database Normalization