3

I am working on an asset tracking system that also manages the concept of "projects". The users of this application perform maintenance activities on their customer's assets, so they need an action log where actions on an asset start life as a task in a project. For example, "Fix broken frame" might be a task where an action would have something like "Used parts a, b, and c to fix the frame" with a completed time and the employee who performed the action.

The conceptual data model for the application starts with a Customer that has multiple locations and each location has multiple assets. Each asset should have an associated action log so it is easy to view previous actions applied to that asset.

To me, that should all go in one table based upon the logical ownership of that data. Customer owns Locations which own Assets which own Actions.

I believe I should have a second table for projects as this data is tangential to the Customer/Location/Asset data. However, because I read so much about how it should all be one table, I'm not sure if this delineation only exists because I've modeled the data incorrectly because I can't get over the 3NF modeling that I've used for my entire career.

Mike Nishizawa
  • 1,410
  • 1
  • 14
  • 14

1 Answers1

3

Single table design doesn't forbid you to create multiple tables. Instead in encourages to use only a single table per micro-services (meaning, store correlated data, which you want to access together, in the same table).

Let's look at some anecdotes from experts:

Rick Houlihan tweeted over a year ago

Using a table per entity in DynamoDB is like deploying a new server for each table in RDBMS. Nobody does that. As soon as you segregate items across tables you can no longer group them on a GSI. Instead you must query each table to get related items. This is slow and expensive.

Alex DeBrie responded to a tweet last August

Think of it as one table per service, not across your whole architecture. Each service should own its own table, just like with other databases. The key around single table is more about not requiring a table per entity like in an RDBMS.

Based on this, you should answer to yourself ...

  • How related is the data?
  • If you'd build using a relational database, would you store it in separate databases?
  • Are those actually 2 separate micro services, or is it part of the same micro service?
  • ...

Based on the answers to those (and similar) questions you can argue to either keep it in one table, or to split it across 2 tables.

peter
  • 14,348
  • 9
  • 62
  • 96