Database Table Design 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



8 comments on “Database Table Design

  1. Brent Ozar

    “Well, you can only have 256 columns in a table. After that you have to create a new one.” WOW. It would be really hard for me not to hand him a whiteboard marker and say, “Sketch out your current database schema.” Violates pretty much every privacy rule in the book, but I’d be so morbidly curious about what he was working with. My fear is that it’d be one 256-column table after another.

    1. Ginger Grant

      Brent —

      I know right! It is unbelievable what people come up with in interviews.

      Thanks so much for reading my blog.



  2. SQLSoldier

    Bonus points for the Ron “Tater Salad” White reference!

  3. Matthew Darwin

    Epic! Reminds me of a schema I saw with tables named 1, 2, 3, 4, 5, 6, 7 and so on. In each, you guessed it, were columns named 1, 2, 3, 4, 5, 6, 7 and so on. When I asked why, the guy who made it announced that he had the descriptions of the tables/columns in a spreadsheet, so he could look them up when he needed to know what they were….

    1. Ginger Grant

      Matthew —
      Ugh. That sounds awful. I would have hated to have to work with that Database.

      Thanks for reading and commenting too.



  4. Kendra Little

    I love your overview of database table design! A great, succinct summary, perfect for folks to review if they’re thinking about how to answer this type of thing in an interview. Thanks for writing for TSQL Tuesday, Ginger!

    1. Ginger Grant

      Thanks Kendra for picking the topic for this month’s TSQL2sday. I had been thinking about writing something like this for a while and you motivated me to go do it.



Leave a Reply