jim.drewe (7/18/2012)

---

There is way too much to cover here in a post. It is something that experienced DBAs can hash over for weeks. But I will share a couple of pointers I have after 26 years as a DBA (mainframe DB2, Unix, and SQL Server). There are some general rules that apply regardless of the platform.

Your logical design should bring closure to the business and IT organization as to what constitutes a completed database design for purposes of the data requirements. The real work now is done by the DBA to guarantee performance while meeting the availability requirements. I will only cover a couple of pointers for operational data (OLTP), not BI/data warehouse physical design. These two are very different.

First, you have to decide whether your physical design will have declared database referential integrity ("RI"). There are many factors that go into a decision like this.

Will the end-user be making ad hoc updates? This sounds crazy in production, but some shops cannot enforce prohibitions against it. If so, then you need RI. Usually though, updates are done with a stored procedure.

Will you be deleting a logical parent and expecting an automatic delete of all logical children? Be careful on this. While RI works faster in this case than explicit logical-child deletes, it also works slower when there are logical-child adds (i.e., under the same logical parent). Also beware of the catastrophic potential that this can wreak. For example, if you delete a department, do you automatically delete all the employees in that department? You might have some employees pretty angry about about getting fired when they were only going to be moved to another department!

Logical-child inserts also cause share level locking on the logical parent. So if you are in an application which has to be updating a non-key row in the logical parent while logical-child inserts are going on, you will experience lock delays or even timeouts.

Second, what type of data will be in your new database? Persistent or non-persistent? If persistent (such as a customer table), then you will have to consider speed in access (typically clustered on the primary key). If non-persistent data that you plan to purge after 30 days, one year, seven years, etc., then you should consider the effect of partitioning. If the data will be read-accessed primarily for a particular period, then have the partitioning based on a date value. You will have a smaller underlying dataset that will be accessed. You will need to test your purge strategy before going into production. Will you need to archive? If so, will you need to perform a recall/restore? Then make sure that is tested as well before going into production.

I could go on about freespace, indexing, logical vertical partitioning, logical horizontal partitioning, and other issues. As I said, it is a very involved, complex subject sometimes.

Jim