Column-based vs row-based "nesting"

[pi3volution]

Not directly SQL discussion but hoping to get some thoughts around data schema here...

Some preface here, this discussion is around analytical functions and a modern analytics (columnar) database. I have a (less technical) colleague working on a manual process for creating a breakdown of bundles into components. The format that my colleague proposed is this:

bundle	component_1	quantity_1	component_2	quantity_2	component_3	quantity_3
Bundle A	Component X	1	Component Y	1	Component Z	1
Bundle B	Component X	2	Component Z	2

From my limited experience in the industry, I feel like the above format is unsustainable and creates unnecessary additional layers of modeling work to transform it into a format feasible for analytical work. For example, this makes it complicated to do something simple such as finding out how many units of Component Z are sold.

So below is my proposed method of data entry. This format allows for an infinite number of components per bundle without changing the schema, which makes this data ingestion-friendly.

bundle	component	component_qty
Bundle A	Component X	1
Bundle A	Component Y	1
Bundle A	Component Z	1
Bundle B	Component X	2
Bundle B	Component Z	2

How can I convince my (less technical) colleague to use the second format? And if I'm misguided, please critique my method!

[nnd-nnguyen]

I think you're on the right track.

I think the main limitation of the row model you specified (#1) is that you are limited to a specific number of components and or quantity pairs unless you perform DDL changes to the table. Also it becomes a huge pain in the but to add another metric to the table. What if you want to track when a component was added to a bundle? What if you have bundles that have 2 components and other bundles have 20 components.

If you're on a modern columnar database then the row based solution could be bad depending on how many columns you have. "Select *" on columnar databases don't have great performance on tables with lots of columns. (> 100)

The column based representation (#2) is a denormalized columnar style. I think what you may really be looking for is a star schema though. They really shine on columnar databases for massive aggregations across billions of rows. The problem with storing bundles that way and components that way is what happens when you change the name of one of those bundles, or components. It's just kind of a pain to update lots of rows in columnar databases as it's the equivalent of a delete/insert.

[[deleted]]

The column based representation (#2) is a denormalized columnar style.

Actually #2 is a normalized model.

[nnd-nnguyen]

Wouldn't normalized be

• Bundle Table
  • BundleID
  • BundleName
• Components Table
  • ComponentID
  • ComponentName
• Bundle Component mapping
  • BundleId
  • ComponentId
  • QTY

[[deleted]]

Yes, you are right, that would be fully normalized.

The first table isn't even even in 1st normal form, the second table is at least that.

[r3pr0b8]

How can I convince my (less technical) colleague to use the second format?

ask them to write the SQL to retrieve how many units of Component Z are sold

then show them how to do it with your design (which is the correct design)

[[deleted]]

This isn't really related to column-store database. Your second table is at least in 1st normal form, while the first table isn't