0

First and foremost, a disclaimer: I've attempted to search for duplicates that answer my question, but I did not find any. Sorry if they exist. Also, I know that questions here are supposed to result in objectively correct answers. However, in a problem that inherently involves trade-offs, I believe that an answer including pros & cons is the closest we can get to objectivity.

I am designing a database that would allow a user to analyze different parts' percentage composition of a whole throughout time. However, the full list of the parts are currently unknown (and can't be known). User input will expand the list of parts as needed. I'm having an internal battle between the data purist telling me I should normalize and the pragmatist arguing, "but the queries would make so much more sense and be so much simpler if it were all in one table."

For the sake of simple explanation, let's describe the problem as a shopping list:

  • Session 1: Anna purchases 12 apples for $5, a gallon of milk for $2.50, shredded cheese for $2.50, and a package of batteries for $10. So Session 1 would result in a composition of [[apples, 0.25],[milk, 0.125],[cheese, 0.125],[batteries, 0.50]].
  • Session 2: The next day Bob purchases 6 apples for $2.50 and 2 gallons of milk for $5.00. Composition: [[apples, 0.33],[milk, 0.67],[cheese, 0],[batteries, 0]].
  • Session 3: Finally, Casey purchases batteries and a new "Item" category, pizza, resulting in this composition: [[apples, 0],[milk, 0],[cheese, 0],[batteries, 0.75],[pizza, 0.25]].

After Session 2, we graph the composition of milk across time and we get the following: [[Session 1, 0.125],[Session 2, 0.67]. It's important to note that at the end of Session 2, we would not yet know that pizza is (about to be) a desired option in the shopping list.

After Session 3, we graph the composition of pizza across time, and we get the following [[Session 1, 0],[Session 2, 0],[Session 3, 0.25]].

One way to record this data would be to put all data in one "Sessions" table (which to a human brain makes the most sense):

Sessions:

SessionID  |  apples  |  milk  |  cheese   | batteries |  pizza
-----------|----------|--------|-----------|-----------|---------
   1       |    0.25  |  0.125 |    0.125  |  0.5      |  0
   2       |    0.33  |  0.67  |    0      |  0        |  0
   3       |    0     |  0     |    0      |  0.75     |  0.25

Of course, the "pizza" column wouldn't be added until Session 3

I think I understand database design enough to know that it would be considered bad practice to alter the Sessions table, adding a column every time a new "Item" category was purchased (even when I could reliably set default to 0 where null..say in prior sessions where that item was not purchased). Instead, my understanding of a potential solution would be moving the "Item" data to two new tables (in addition to the Sessions table, which would contain other information particular to each session not included in the sample table above such as ShopperID, etc.). My understanding is that the larger # of potential "Items" there may be, the more sense it makes to normalize like this:

Items:

ItemID |  ItemName
-------|------------
   1   |  apples
   2   |  milk
   3   |  cheese
   4   |  batteries
   5   |  pizza

SessionItems:

SessItemID |  SessionID  |  ItemID  |  Composition
-----------|-------------|----------|--------------
   1       |      1      |    1     |    0.25
   2       |      1      |    2     |    0.125
   3       |      1      |    3     |    0.125
   4       |      1      |    4     |    0.5
   5       |      2      |    1     |    0.33
   6       |      2      |    2     |    0.67
   7       |      3      |    4     |    0.75
   8       |      3      |    5     |    0.25

This seems to be the "best" from a pure relational perspective. But it also seems that it will be a huge pain (and perhaps other cons) in practice when I am trying to first JOIN three tables and then analyze the trends across time in the joined tables.

Additionally, let's say I have a fair level of confidence that the list of "Items" in my actual database will stay relatively small...let's say 98% sure it will stay less than 10 items. Would it make more sense in this situation, for the sake of query simplicity, to stick to one Sessions table altering it by adding columns for the additional "Items" as needed?

In Conclusion: What are the pros and cons of normalization vs. denormalization in a database that includes categories, the number of which will increase through user input over time, especially when the ultimate goal is to analyze the trends of those categories over time?

  • 1
    This is not about normalization, but rather "tall" ("long") vs. "wide" table design. The internet is full of opinions in favour of each approach; choose whichever you agree with more. You'll probably change your mind later either way. – mustaccio Jun 24 at 18:52
  • @mustaccio I think it is about normalization. Couldn't apples, milk, cheese, batteries, and pizza, alternatively be called Item1, Item2, Item3, Item4, Item5? In this case, wouldn't it violate 1NF because it is a "repeating group" of columns? As for "the Internet," I know that there are many opinions existing there. However, I tend to trust this platform as an effective method for ranking existing opinions, which is why I asked the question here after reading several resources on the web. – snoski Jun 25 at 0:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.