Stuck in a database structure with many tables
I am creating a DB structure by E / R Diagram, but I have been stuck with some days on this issue. I may be wrong about how I do it, so if you think I can do it better, it would be great :)
Scenario: I have N users who own N sensors, not all sensors (may grow up to 300 kinds of sensors in the future) have the same functions (columns), so I suppose I need a table for each sensor and then a list inside the values collected.
I have some doubts as to how to list the tabless for "kind sensor" - "Sensors" where columns should be put into "sensor" table, also this way I will get many tables. Do you have any hints?
source to share
Maybe it is better to introduce many relationships between sensors and the functions that they have? For example, for example:
Sensors
sensor_id (pk)
type
Features
feature_id (pk)
name
SensorsFeatures (Ownership table)
sensor_id (foreign key to Sensors.sensor_id)
feature_id (foreign key to Features.feature_id)
If you need to store the values of these functions (weight, height, width, ...), you can do so in the SensorsFeatures table.
source to share
The simplest and easiest way to do this is to make all specific columns in the "sensors" table and have one foreign key to the other table "sensor_type" which has two columns
table "sensor_type"
id_type - identifier (primary key)
description - description of your sensor (heat, temperature sensor ... )
Then your table gauge looks like
table "sensor"
id_sensor identifier (primary key)
id_type foreign key references sensor_type table
size default null, if type 2 or 3 then something
weight default null, if type 1 and 3 then something
etc...
You need to understand that this is probably one of the many solutions that might solve your problem. Hope this helps.
source to share