Physical Database Design Elements

by Arlyn R
In the article, “Physical Database Design Consideration” by Greg Larson, it answers and provides some answers to questions that come up in designing a physical database. The author provides information and some guidance to help the designer understand the outcome from given choices. Larson covers data types and the difference between variable and fixed -length characters, whether to allow nulls or not, different date data types, and index guidelines. The article advises to use CHAR and NCHAR data types for data that have similar length size and do not have many NULLS. These columns will most likely be populated with the same range of characters and the space for these columns will be used up no matter what. Therefore, VARCHAR can save storage space if the data type can and is frequently NULL.

In regards to whether to use Non-Unicode versus Unicode, Larson explains changing a database to support Unicode can be much more expensive than purchasing hardware to handle the larger character set. The author goes on to show the storage size differences between integer values and suggests to scale up to the next larger integer type because only a small amount of disk space is wasted and makes the database flexible to future changes. Larson displays a table to show the six different date data types SQL Server 2008 offers and to obviously choose the one that is applicable to the business rule. Finally, the article gives five things to consider when creating indexes which provide short cuts to database queries. Greg Larson recommends to “‚Ķfind the balance between conserving disk space and supporting future data requirements,” which this article efficiently supports.

Now that we have entered into the physical database design, this article proves relevant especially in the details of data types. I found the articles explanation on when to use VARCHAR versus CHAR helpful. I agree with the author that using Unicode character sets is best even if the business is operating in one national economy. In my opinion, most businesses strive to enter and sustain in the global market and so creating a physical database to support the business’s future will make it robust. This article was very informative yet easy to understand and covered situations that prove helpful as we start to transform our design to a physical database.

Larson, G. (2011, December 21). Physical Database Design Consideration. Retrieved from http://www.databasejournal.com/features/mssql/physical-database-design-consideration.html. Retrieved on October 28, 2012.

2 thoughts on “Physical Database Design Elements”

  1. I had also blog on an article that helps explain which data type to use when designing a physical database. I find that the goal to choosing data type is to choose the right option that requires the most minimal storage space within the most possible number intervals of characters. The article you had blog about goes into more in depth with CHAR, NCHAR, and VARCHAR while mine gives an overview of various data types. Overall, these are two very good articles.

  2. Thank you for posting this Arlyn. I remember in the last project I was confused about which datatypes to pic and I opted for using the datatypes that would maximize storage space and are recommended by professionals (after a quick search on google). I didn’t come across anything about CHAR or VARCHAR properties so in the next project I’m going to do my best to use those properties if they are in fact recommended. I have never heard of the NCHAR property so that is something I’ll still have to look into – I’m also confused as to why multiple data types have the same purpose but I suppose we will be going over that in class.

Comments are closed.