Monthly Archives: July 2012

Skinny vs Fat tables pt 2

continued from part 1

Skinnier can very often mean the tables are more normalized.

If you go through a process of normalization, you might find that the higher levels of normalization transform your tables to become skinny and make them easier to relate to other entities, especially when the tried and true practice of using a primary key for all tables is used.

downside of skinny tables 

If there is a downside of skinny tables it is that when you make them skinny, you are reality splitting them into two or more tables. As such skinny tables have lots of skinny table siblings. So, normalization also tends to add to the number of tables, is that such bad thing? Well it could down to a preference of the developer of the system.

If you enjoy joining related tables and find it kind of neat, skinny tables are a good thing.

I think back to earlier days when I often preferred to pack more columns in around a primary key. In many cases the end users can’t tell of any difference, however when it comes to maintenance and managing security, I believe in the end, skinny tables win out in Online Transaction Processing (OLTP) database projects.

The benefit in skinny tables 

Much of the benefit in skinny tables comes from adding joins (foreign keys). These foreign key relationships add to better data integrity; a very good thing to have in a DBMS. This type of constraint is another way to manage the data in the database and in my opinion, adding foreign keys is like compound interest in a savings bank. The longer they are in force, the more you get back in return.

Do skinny tables deserve this much attention? Anything that helps you design a better system for your particular needs is good right? In that spirit, although It’s a little hard for me  to describe in exact terms, skinny tables, I have found, allow you to, with greater ease,  add new tables (think new features) in a way that accomplishes a natural evolution of the business aims of the system. Since you are preparing for something you anticipate but cannot predict the actual specification of, being flexible means being prepared for anticipated future changes.

What is to like about fat tables?

Fat tables tend to have more likelihood of having duplication of data so what is there to like about them?

As mentioned earlier, a danger of such tables to be watched out for, is the update, insert and delete anomalies.These happen when you want to do ‘one thing’ but due to the structure of the table, sometimes because it is too fat, duplicates end up in the table. So, you frequently end up having to do ‘many things’ when you update a record. And if you don’t do the ‘many things’ properly, data inaccuracies result. Let us say you track temporary employees , for each employee you include the temporary employee’s temp agency name.  When a temp agency is sold to another company, its name changes. If you have many employees, you much update the temp agency for each employee.

Trade offs Skinny vs Fat Tables

So, does this mean you always use skinny table structure versus fat table structure? In cases where you feel the trade-off in having a fat table benefits you more, then go for it. For example,  if you have a database designed for the sole purpose of displaying historical data a fat table works just fine. Likewise, If there is no need to update the data, and you only select it, FAT table structure  is ideal.