CODEWITHSUNDEEP
mysqldatabaserelational-database
Augusto Peres
Augusto Peres

Reputation: 285

Multiple tables with same columns or one table with an additional FK column

I am working on a project where I have sensors that will store some data (let's say for health monitoring) into a Database, for retrieval later via an API. I will have the same set of sensors (about 10 different sensors) in multiple locations. That data will be stored in the DB every five minutes. So every 5 minutes I will have a new row for each set of sensors.

Since I will have multiple locations, and this needs to be scalable, I don't know if I should have one table for each location or if I should have one huge table with an extra column to identify which location that row belongs to.

For example:

Upvotes: 1

Views: 1793

Answers (1)

PerformanceDBA
PerformanceDBA

Reputation: 33708

So every 5 minutes I will have a new row for each set of sensors in my DB.

Well, it would be very silly to INSERT a new row just because it came down the line from the Sensor controller. That would be unscalable, you will fill the database with data that does not changes: duplicates.

  • You need to INSERT a new row only when one of the data values in the row has changed since the previous row for the (Station, TimeStamp).

Since I will have multiple locations, and this needs to be scalable, I don't know if I should have one table for each location .

That has nothing to do with scalability.

That is simply a gross Normalisation error.

or if I should have one huge table with an extra column to identify which location that row belongs to

That is Normalised.

It is not an "extra" column, it is an ordinary, Identifying column.

The table is not "huge" or "massive". The sum total rows in the database are the same for either option. The index will handle the queries just fine.

  • Commercial RDBMSs handle millions of rows per second. MySQL is not commercial, does not even comply with the SQL requirement (the use of the term SQL is misleading), and it does not have a Server Architecture. Therefore do not expect commercial performance from it. However, it should be able to comfortably manage INSERTing 0-to-10 rows every 5 mins.

For example: If I have 10 monitoring stations, should I have 10 tables with same columns and different names (station1, station2, station3, station4...)?

As explained above, definitely not.

Or should I have a massive table that will get [zero to] 10 new rows every 5 minutes with an extra column specifying the location of the monitoring station? (row: timeStamp, bloodSugar, heartBeat, ..., stationName)

As explained above, yes.

Relational Data Model

AugustoTA

The Fact of a Station (which has certain attributes), and the Fact of a Reading (which has certain attributes), are two separate things.

Note • Notation

  • All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

  • My IDEF1X Introduction is essential reading for beginners

Upvotes: 2

Related Questions