CODEWITHSUNDEEP
sqldatabasesqlitenormalization
user6757597
user6757597

Reputation: 23

How to properly arrange this type of schema?

I want to allow the user to be able to create whatever metric they want and record values for it over time. The only action outside of that would be to be able to view the data trend for a chosen metric / timeframe.

I structured my tables like this:

Table: Metric
Fields: id int prim key, name text

Table: MetricPoint
Fields: id int prim key, metric_id int (foreign key to Metric.id), value real, timestamp long

Is this the correct way to do it? Or should I be dynamically making new tables for each metric that is added? Or making them columns?

I had been told this current arrangement was bad because it used Entity–attribute–value modeling, which is considered an anti-pattern apparently: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

Upvotes: 2

Views: 42

Answers (1)

jleach
jleach

Reputation: 7790

Do NOT make new tables for each metric. Do not add new columns for new metrics. Your approach seems to be correct.

When you can enter new data without having to redesign/create new objects, then you are on the right path.

Regarding EAV: that is more or less when you don't know what the schema is, so you try to build a schema that can cover whatever might need to be covered. It's a nightmare and shouldn't really be used. In your case, you know exactly what the schema is (metric, metric attribute), just not what the values are, which is perfectly fine.

Upvotes: 1

Related Questions