Subtitles of the Movie

When designing a database, we always keep longevity in mind. We want to go for a truly sustainable database that will be going on and on as time passes. To do this, it's best to follow the best practices. Let's go through and talk about each of these best practices and then put together a checklist for yourself. Make sure you're following these suggestions and recommendations. First, the basics. When we talk in terms of the basics, we want to start with our tables. Make sure they are meaningful. Remember, we are going to have our primary tables that will work as storage tables then we'll have our foreign tables that use that data. Foreign table also is typically where we do our input. Make sure you are using separate columns for data that's accessed from those primary tables. Avoid using a combination of two, three, four columns. Make sure you're just linking to one. That's why all tables should have a key. This is the primary key. It will act as the glue that keeps the record together and it will act as the link between tables. Remember, your tables have the primary key that is related via the foreign key. Also, avoid redundancy. We only want to see each piece of data once if possible. Of course, there's always times when redundancy will not work. And minimize those empty cells as much as possible so that your database?? so that there is an actual entry going into each field. Remember, the use of defaults is a great way to avoid the empty cells. We also wanna put some time into the environment, specifically the design and the coding of your queries. Remember, poorly written query will slow down your entire system. Make sure that everyone writing queries has been educated in this procedure. Also, the structure of the tables or any other views used by the query becomes important. I can write the perfect query but if I am piggybacking on someone else's query that was poorly written, I'm still gonna have a badly running query. And watch those indexes and your statistics. Remember, the indexes are a very tricky but a very key component to actually working with your database. Test, re-rest, run the analyzer, see what the impact of the type of index is on your system itself and then remember your whole system. We've got to put consideration into what type of bandwidth are we using, what are the resources available on our server, what are the resources available on network itself and then remember currency issues. We always have more than one user in that system at a time. We need to make sure that we can handle that activity. Now, on the aspect of odds and ends, remember to get rid of sub-queries when you can. If you can write it in one query without piggybacking on another, please do. We want to try to considering using UNION ALL instead of just UNION. The less that we're giving, as far as commands and instructions, the better. We also want to understand the impact of triggers and constraints on performance. Remember, along with the functions they could suppress the indexes. I'm giving a set of instructions that I have to have followed by the data base while I'm running all of these triggers. In our next set of videos, we'll be talking about this in more detail. Also, correctly utilize those temporary tables. They are a great way of having a virtual table that closes off every time we close the database and be consistent. In everything that you're doing, be consistent, especially with optimization. We want to get rid of cursors when possible and we want to ensure that we can do everything to support the quick running of the database itself. Now, to recap the top best practices. Concentrate on design and planning. Follow the rules of normalization. Keep consistent with your naming standards and document your data base. Use your identity columns as your primary key. Remember we want that key field as small as possible. Protect your integrity. Concentrate on security. Concentrate on triggers, constraints and other components. Use your stored procedures when possible to axis data and test and re-test and test and re-test. Remember, your data base is progressive. Build it once and then never give it anymore attention.