Thus, business decisions often affect the database design, too. In this case, if Northwind Traders opts to reserve the inventory by decrementing the UnitsInStock column in the Products table, the exclusive lock required to perform the UPDATE statement will block the data flow for everyone attempting to buy that item. If multiple customers attempt to reserve multiple items at the same time, you have a great risk of a deadlock condition, which stops data flow for everyone involved.
This blocking problem arises because the Products table is central to so many operations; the solution is to eliminate the single point of failure. One approach is to permit dirty reads—read operations that ignore the exclusive lock. For the Products table, letting customers see inconsistent data isn't a problem because most of the columns contain static data, such as product name and supplier name. You need to be careful only when users view current inventory levels. The exclusive lock placed during the UPDATE statement that changes the inventory levels serializes access to the row on a first come, first served basis, but allowing dirty reads will let customers see inventory levels that might be greater than what's actually on hand. If too many people attempt to order too much of one item, inventory levels could even drop into negative numbers. Although many applications use negative inventory to indicate how many items are on back order, items such as rare wine can't be reordered. In most cases, you can resolve the situation by using an UPDATE trigger to check for negative inventory numbers. How you handle this type of problem is a business decision, and it will affect your overall database design because it affects the meaning of the values in a table.
Understanding the orientation of the user to your data is important. Knowing how many people will use the database simultaneously as well as what types of operations they'll perform, how often, and on what data will help you maximize data flow in your database design. Typically, bad designs come from either ignorance of or bad guesses about these factors; therefore, they should be the first things you consider when you start the design process.
Find the Right Balance
Although users will have the biggest effect on your database design, the choice of hardware will significantly affect its ultimate success or failure. Slow hardware can exacerbate a bad design, whereas fast hardware can mask many problems. Unfortunately, many programmers depend on fast hardware to save them from having to think too hard about the databases their applications use.
As an example, one of my clients, a Web site hosting service, originally stored all its customer and dynamic Web content in a Joint Engine Technology (JET) database, which its Active Server Pages (ASP) application accessed through ADO. The database was a complicated mess of interrelationships between tables because of a complex commission structure and because all the elements of the users' Web pages were stored in the database itself. When the number of users grew from 1000 to 5000 very quickly, the client moved the database to a single-CPU server that was twice as fast. That server worked until the number of users approached 10,000; then, the client moved the database to a four-CPU Compaq server with a six-disk RAID 5 array. The database was only about 20MB, but its design was so poor that users were interfering with one another. The inefficient locking mechanisms that JET uses were a problem, too. Moving to a faster machine just meant that each operation finished faster, which resulted in each user using less physical time. This approach worked in much the same way that a new lane on the highway helps lessen rush-hour traffic jams.
When the number of users reached 15,000, my client was forced to move the database to SQL Server and build indexes that were appropriate for SQL Server. We left the database design alone until the number of users reached 60,000. At that point, the application could handle approximately 1000 concurrent users updating the client's Web pages—an unacceptably low percentage of the total. Because the database was already on a quad-CPU system that was clearly not overutilized, moving to a faster server wasn't going to help. Even moving to multiple servers wouldn't help because the impediment to data flow was in the database itself, not the hardware. The only choice was to change the database design. The lesson I learned from that experience is that although hardware can mitigate problems that a bad database design creates, fixing even well-established databases ultimately becomes more cost-effective than adding memory, disks, or CPUs.
A common misconception is that full normalization of the data is the ultimate goal of database design. Normalization has the benefit of ensuring data consistency and minimizing redundancy, but if taken to the extreme, it can have the side effect of impeding data flow from the database server.
Prev. page
1
[2]
3
next page 
