Table Design Considerations - SQL Server Brain Basher of the Week #063

He is the Brain Basher or the interview question of the week. This is something every database engineer should know and it is not limited to database administrators.

What would be the considerations you make when you are asked to create a table for holding data?

Let me elaborate the question more. How do you start designing of the table when you are asked to design it on a particular entity. If you are an ordinary engineer who thinks traditionally, your answer could be;

"Need to check the requirements and create a script with relevant columns, and apply normalization as need."

Do you think that the answer is similar to cartoon shown above? 

Being an interviewer, I prefer to hear something more, something that explains the interviewee's experience. Let's point out some valuable considerations we have to make when a table has to be designed.

• Read the attributes identified by the BA and understand the business requirement - yes, you can think that this not something we should concern but trust me, you need to know.
• Analyze and see whether this needs as a normalized table or denormalized table. Based on the requirement, decide the level like how far we need to go through normalization rules.
• Decided the data types of the attributes and size of them. Need to pick the best, thinking present and future too. Should we think about past data as well. Yes, we should if we need to load legacy data.
• Consider the datetime format and whether it needs to maintain Unicode characters.
• Need to analyze whether it needs partitions and partitions need to be distributed among multiple file groups (preferably multiple volumes).
• Constraints required. In addition to the Primary Key, we may add additional constraints such as Foreign Key, Unique Key and some rules.
• Whether Artificial or Surrogate Key is required.
• Number of indexes required, whether we need Columnstore or not, order of them, whether they need any filters.
• Whether compression is required.

There are few other tiny things as well but these things show that the Interviewee is smart and knowledgeable. Remember, it is not just these points, knowing details of them is a must.