CODEWITHSUNDEEP
mysqldatabase-design
ryanrr
ryanrr

Reputation: 71

How to design a MySQL table that tracks the Status of each Asset, as well as every old Status?

I would like to create a table that tracks the status of each asset as well as each past status. Basically I want to keep a log of all status changes.

Do I create a timestamp for each updated status and have every update be its own separate row, linked back to the asset through the assetid? Then sort by the timestamp to get these statuses in order? I can see this table getting unwieldy if there are tons of rows for each asset and the table grows linearly over time.

This is for a MySQL database.

Upvotes: 0

Views: 272

Answers (1)

T.Nylund
T.Nylund

Reputation: 777

Here is an example of how I have designed a database table to track/log purposes.

Columns:

  • auto increment pk (if you don't have better pk)
  • timestamp
  • tracked object id (asset_id in your case)
  • event type (probably you don’t need but this is explained below)
  • content (this can be also named status in your case)

My example is very simplified but the main idea is to insert each record into own row. You can create a table with proper primary keys or indexes to have a good search performance.

Using the structure you should be able to search by asset, by status, or get latest changes etc. The structure depends on your needs so usually I have modified it to support the need.

Don’t care too much about the event -columns. I just put it here because most of the implementations are based on event sourcing. Here is a link to one article that could explain it: http://scottlobdell.me/2017/01/practical-implementation-event-sourcing-mysql/

I suggest that you could read more about that event sourcing that if the design could work in your case. Look only the database example because that is similar like in my example.

In the results, you should have a journal of status changes. Then it depends on your code how to handle/read data and show results.

About the linear growth… I would say it is not a big problem. Of course, if you have more information what “tons of rows” means, then ask. I have not seen any scaling problems. The same structure works very well with relational or with NoSQL databases. Mysql also has features to optimize that kind of structure if the size of data will be a problem.

Upvotes: 1

Related Questions