Let's talk about something common for all database management systems without talking something specific to SQL Server. This is the interview question of the week and let me start it with a conversation I had with a interviewee.
image taken from: https://www.pinterest.com/thepotentmix/recruitment-cartoons-and-other-entertainments/
Me: Let's assume that you have been given a document that contains identified attributes for a particular entity, for an example, Customer Entity. Business Analyst has mentioned that it needs an attribute called Customer Name. How do you take this entity and design your database table?
Interviewee: All I have to do is, understand given attributes and create a table call Customer with relevant columns. We need to make sure that the right data type is selected for each every attribute or the column.
Me: How do you decide the data type? Let's talk about this specific attribute: Customer Name.
Interviewee: Yes, I will be setting varchar for this attribute, probably with the size as 200. Another thing, if the solution is a multilingual application, have to use nvarchar instead of varchar.
Me: Good, anything else to be considered on that?
Interviewee: Regarding Customer Name, I think that is all I have to consider. We might have to change the size of it but 200 is reasonable.
Okay, what do you think? He is not a senior person, that is what I started with basic but I expected something additional, something extra.
First thing you need to understand is, BA does not know or does not think about database design. It is all about the business. It is our duty to covert the business identified to logical and physical database design. So, even though BA has identified Customer Name as an attribute, or as a single attribute, it does not mean that we need to stick into it. This is where we apply normalization rules. Not only that, we need to think and see;
- How this attribute is going to be filled
- How this attributed is going to be read
- How often the entity is searched based on this attribute.
For an example, Customer Entity (or the table) might be searched by Customer Last Name. Have we addressed this requirement. Do not say that BA has not mentioned it, if not mentioned, it is always good to get it clarified but it is something we need to consider.
If we consider other common things whether BA has mentioned it or not. Customer Name is not going to be a single column. You will surely have at least two columns called First Name and Last Name. This makes sure that your table is designed properly and it is ready for standard or most common analysis as well as holding data efficiently. Remember, breaking an attribute to multiple columns is not only for known attributes such as Employee Name, Location. We might break attributes like Product Code, Invoice Number as well. For example, Product Code might be forming using two elements such as Product Type Code and Unique Number. In that case, it is better to have two columns for Product Type Code and Unique Number, and another Computed Column for Product Code (if required).
Make sense? Although this is a simple thing, many miss it, hence DO NOT FORGET SIMPLE THINGS AND FUNDAMENTAL RELATED TO DATABASE DESIGN :).
No comments:
Post a Comment