SQL - Structure
The importance of building your tables correctly could be considered its own form of art. Psychologists have even gone as far to say that those with the ability to organize their thoughts and concepts well tend to be absolutely brilliant (ie Mozart, Einstein, DaVinci). A database is only slightly different from the human brain and clear organization is a must, especially if large quantities of people will be using the same database.
Designing the right database has a lot to do with what you need to accomplish. A database with thousands and thousands or even millions of records requires a completely different solution than a database that will only have a few hundred records. Think about the future and plan ahead.
SQL - Table Relationships
The key to a large database is having tables with material that can relate to one another. A great example is the employee ID. Ever wonder or joke about being just another number to the company. A lot of that has to do with the way they set up their database. A large company is going to have a weekly payroll and a personal information table. They might be set up like the following:
This set up proves useful in several ways. The tables are smaller, more compact, and precise, allowing for maximum access speeds with each query. We can also moderate who has access to each table, meaning that our accountant can have access to the payroll table, but not the personal information table, eliminating security risks.
Relationship tables are the choice for high-traffic databases. There are countless security advantages, faster query returns, and complete view customization (more on this later).
Found Something Wrong in this Lesson?
Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!