Which Data Type Should be Used for Physical Database?{Comments Off on Which Data Type Should be Used for Physical Database?}


by Polun L
The article, “Physical Database Design Consideration”, by Greg Larsen, talks about what data type should be considered while designing a physical database. The author mentions that selecting a correct data type for a column determines the database performance and the cost of changing application code in future. First of all, two character data types, “fixed length data” and “variable length data” are very hard for designers to decide which is an appropriate data type for a column.  Grey points out that fixed length data type is for columns that store the same length value in each row. Even though the data is not exactly the same length, it is still good as long as there are not much nulls. The reason is that the fixed length data types, which are CHAR and NCHAR, take up a lot of space even though the column does not have any value. If database designers think that a row or a column will occur a lot of nulls, they should select the variable data types, VARCHAR and NVARCHAR. Because nulls value does not take up disk space under variable length data type, it helps SQL Server to maximize the efficiency for storing date value. Next, the second topic is about Unicode and Non-unicode. SQL Server needs to store different characters as we all know different languages have their own characters. Unicode is the best option for storing different characters; however, it requires a lot of disk space. On the other hand, non-unicode uses less space because most non-unicode is stored using a single byte. Then, Grey talks about data type size and allowing nulls or not which also affect the data performance. In the article, there is a table of storage size for each data type size, but it does not really matter for choosing which data type size because nowadays disk space is enormous. As for allowing nulls or not, we first need to know what the consequence is between null and not null. Null has three different outcomes which are true, false, and unknown. According to Grey, the outcomes can make the application logic  more complicated if null value is allowed. Inversely, not null value can eliminate possible errors along with null values. In the end, Grey suggests that having default values for columns would be a better choice because it is able to identify a column to be null or not null.

Reaction:

All in all, the benefit I have got from reading this article is that the data type, null or not null, and data length character are very essential when a database is designed. In the past, I did not think they were so important because fixed length or variable length was no difference for me as long as it took the input to the database. Also, I did not have much knowledge of null and not null, but now, I know what they do in the database. The decision of selecting a right data type is very important because it may affect the database performance. Therefore, I need to find out what a new database is used for and what kind of information I want the database to store. Then, I will be able to select the correct data type for the new database.

Reference:

Larsen, G. (2012). Physical database design consideration. ITBUSINESSEDGE. Retrieved from http://www.databasejournal.com/feedback/http://www.databasejournal.com/features/mssql/physical-database-design-consideration.html