Special care/settings for databases that hit a few tables in particular

Question

I suspect that lots of business related software is designed to manage a particular set of information; customers, orders, leads or products and/or a combination. This has been the case in most of my experience. All other tables are usually data supporting the major ones.

If this isn't the case, then assume it is for a moment :) because in my case, there's really only 1-2 main tables (of about 20-30) in our DB; namely the customer table.

Is there special care/settings applied to these types of tables to make the constant hitting that the table will get easier to manage or to keep the size reasonable? I'm not at the point where I need to do something about it right now because of performance or anything, and I might not be for a while, but when the time comes, I'd like to know where to start.

I've tagged mysql, but really, any info about other techniques used with other RDBMS's is still useful.

Answer 1

You might take a look at table partitioning for very large tables.

Answer 2

It's reasonable to concentrate on the key tables when considering where to spend your limited amount of optimization time. Basically, all the good practices for table design you want to make sure you really do on the heavily used tables.

• You want to make sure the primary key is good. Usually this will be an autonumber integer. Also, make sure the clustered index is based on this key. (this is basic stuff you already know but I have to get it out of the way.)
• replace repetitive data with numbers relating to look-up tables, for performance and data integrity
• Use the proper data type, but the smallest possible, but don't take chances with size. (This is less important than a lot of other things, as long as you don't have huge char fields all around where numeric fields would do.)
• Indexes! You must have them. Think about them when designing as part of your overall scheme, but they can also be applied well later for great effect. Understand covered indexes. Customers sounds like something that's queried against a lot more than it is updated. If so, do not be shy with your indexes.

However, even though a small number of tables are by far the most heavily used, it's still your overall design scheme that's most important.

• Have your data Normalized. This is the key to everything. Don't be worried if you end up with a lot of tables with seemingly related data all over the place. Much worse is cramming data that isn't quite 1 to 1 into existing tables. Modern DB's a great at dealing with multiple tables; but they can't data relating to each other incorrectly.
• have good, simple foreign key relationships. The first column on any table should usually be an autonumber key with a name similar to the table (CustomerID for the Customers table)
• above all have your tables well named

This is really a massive question you've asked. I'll summarize by saying to concentrate on the key tables, but it's how that table relates to others that matters most.