There are a number of different ways that you could decide to organize your data in a database. If you are creating a database to be used in a transactional system, your table design should follow a normalized design as much as possible. Data should be grouped in logical groups, such as customers, products, sales, orders, quotes, tickets etc. Redundantly repeating data in multiple places will cause problems in the future. Your design may include hundreds of tables, and that is perfectly fine.
If the purpose of the database is for Power BI or for a data warehouse, dimensional modeling techniques should be deployed. In this type of database design, generally speaking there are a number of tables containing descriptive data, such as product and customer and few fact tables which contain the actions which happened. The actions include things like Sales. The database design will look much like a star with the fact tables in the center and the dimension tables connected to it like satellites. If you have one dimensional model connected to another dimension, that design is called a snowflake and some applications, like Analysis Services Multi-dimensional will not process it well. Power BI and Analysis Services Tabular work very well with snowflake dimensions.
Snowflake Data Model from Power BI
Table Design Gone Wrong
To paraphrase Ron White, the reason that I described database modeling is so that even people who know nothing about database design could appreciate my interview story. When I was working at a previous location, I assisted in providing technical reviews for database developer jobs. We asked a number of typical questions about indexing and stored procedures, but I always tried to come up with at least one question which the candidate could not readily answer by cramming interview questions found on the internet. I decided to ask one candidate, who did correctly answer the previous stock questions something that would let us know what kind of work he had really done. I asked him “What do you do to determine how to design a table?” I was interested to find out what his thought process was, see if he would mention normal form or describe something he had done in the past. I was completely surprised by his answer.
“Well, you can only have 256 columns in a table. After that you have to create a new one.” This answer was a complete surprise. I was really curious to find out where he had developed this completely warped view of how to determine what fields should go in a table. It turned out that he learned all of his database skills from a co-worker, who had recently retired. His co-worker had worked at the same location for a very long time and when he started used mainframes without any databases. He had migrated some of the applications to databases and they wrote them this way because it “made sense”. After that the interview was over, and we hired someone else.
I challenge anyone who is learning databases to please look up what people tell you to do on the internet. This is useful for two reasons, the first being that it will probably help you learn the concept better to read about it another way. The other reason is that you can find out if the person teaching you really knows what they are doing, so you will learn the correct way to do something.
Yours Always
Ginger Grant
Data aficionado et SQL Raconteur
