3

I'm creating a structure of DB by E/R Diagram, but I'm stuck since some days on this problem. Probably I'm wrong in the way I'm doing it, so if you think I can do it in a better way, would be lovely :)

The scenario is: I have N users who owns N sensors, not all the sensors (in the future could increase to 300 kinds of sensors) have the same features(columns), so I suppose I need a table for each sensor and then list the inside the values collected.

structure

I have some doubts regarding how to referentiate the tabless for "kind sensor"-"Sensors" wich columns should I put on the "sensor" table, also in this way I will get many tables. Do yo have any hint?

notme
  • 451
  • 6
  • 17
  • I think that you should list all features as columns in one table and the sensor that does not have a specific feature , the cell for this feature should be set to null . – Ahmed Muhammad Aug 04 '15 at 11:00
  • @DrewPierce a user can have from 1 to N sensors. If I use columns, i could probably get 300 columns, where for each row it's just one or two cells who are not nulled. Is it good to it? And the tables here are just a sketch, so i'm very openminded to start over again :) – notme Aug 04 '15 at 11:09
  • @DrewPierce It's the opposite. 2 or 3 columns filled for each row only. Meaning almost 297 nulls for each row. Btw In near term I might have 50 types, but in long term could scale to 300. – notme Aug 04 '15 at 11:16
  • You are trying to implement inheritance in a relational model. [Here are the most common approaches](http://stackoverflow.com/q/695752/1446005). (your question is a duplicate but you have my +1 for a very well formed question, thank you). – RandomSeed Aug 04 '15 at 13:27
  • @RandomSeed you gave me the best and most complete answer with your link, why don't you answer with this comment, so I can mark you as best answer? Thanks! – notme Aug 05 '15 at 15:19
  • My marking the question as duplicate is my answer :) Do upvote answers in the other topic if you found it useful. – RandomSeed Aug 06 '15 at 08:01
  • i faced a very similar issue without writing my case i was wondering if there is any chance the table name of kind sensor and an the primary key of the particular row. Is this possible? – Andrewboy Nov 26 '16 at 10:36

3 Answers3

2

Simplest and easiest way to do it is to make all specific columns in your table "sensors" and have one foreign key to another table "sensor_type" which is consisted of 2 columns 

table "sensor_type"
id_type  - identifier (primary key)
description - description of your sensor (heat, temperature sensor ... )

Your table sensor then 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 this is probably one of many solutions which can solve your problem. I hope it helps.

KuKeC
  • 4,392
  • 5
  • 31
  • 60
1

May be it's better to introduce Many to many relationship between sensors and features they have? For example like this:

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 values of these features (weight, height, width,...) , you can do it in SensorsFeatures table.

Vasily
  • 1,858
  • 1
  • 21
  • 34
0

You should add new table e.g. sensor_type for all sensors, where each row will be the sensor with uniq id which can be added to user.

The idea is to separate sensors with type or something else which is the most common thing beaten sensors.

Gevorg Margaryan
  • 159
  • 8