Skinny versus Fat tables

Database Design involves many choices, frequently it comes down to the experience and comfort level of the database developer when the actual data structures are. The most important structure one could say is the logical repository of the data itself, the table.

Comprising rows and columns, tables are a wonderful structure for holding data.  And like people, plants and animals they come in all shapes and sizes. The two distinctions I often make are skinny or fat tables. I have a confession to make, I notice I tend to like skinny tables over fat tables. But before we go to deep into the why’s of that, what is a ‘skinny table’?

I consider a skinny table to be a table with 2 columns, one of which is a primary key. However a skinny table could be a table with a collection of columns that the developer considers to be few in quantity. So typically you would have between 2 and a ‘few’ columns, say 3 or 4 at most.

If there is a classic skinny table,  the column’s data types at least not large, like VARBINARY(MAX) data, BLOB or CLOB data.  Such columns could have a very large size.

If I were to deal with such Max data types, I would probably want to just have a two column table with a primary key and some other linking tables around it,  to fill out any needed details. If a table can be associated with a given ‘file’ and if that file could be backed up and managed separately (if needed), it could be a very nice neat way to handle large binary data even if we would prefer not to.

Business requirements often impose on us the need to included large volumes of non-organized binary data, such as images, video, audio or document binary files. Honestly, I’m not completely comfortable with having them in a database table but if an organization needs it, the fact that it can be stored as part of a relational system and the benefits of added security and integrity weigh in favor of their inclusion.  When I’ve run into this sort of thing, I tend to think for of being skinny.

I can think of two reasons why ‘skinny tables’ tend to favored. One is, for On Line Transaction Processing systems (OLTP), higher levels of normalization tend to be good things because data is stored with less duplication, it means the DBMS can update a record with less effort. Contrast this with a fat table. Fat tables tend to have duplicate data. As such, for fat tables you generally have perform actions on several rows at a time. I’m thinking of the famous update, insert anomalies mentioned in other posts.

Another reason has to do with the ability to keep the database design more flexible. I’m thinking in terms of making it easier to add additional features and capabilities to the system. This is a very subjective thing but that said, I’ve noticed in databases that I’ve worked with, it is easier to add features when the tables are skinnier.

 Continued