Search code examples
databasedatabase-designentity-relationship

Implement specialized entity in ER diagram

I'm designing a db and find a situation where im not sure what to do, so first of all lets ilustrate part of the ER diagram with an image:

ER diagram

As you can see, i have the following:

  • A device produces 0:N data's
  • The data produced can be of different DataTypes (For example, a device can produce temperature data and humidity data at the same timestamp)
  • A specific data was produced only by 1:1 device
  • 1:N relationship between Device and Data. Since data is a weak entity (cant exist data without devices), i'll have a composite PK in Data formed by ID_DEVICE and TIMESTAMP (not represented in image)
  • Since a specific data can only be of 1 type, i have specialized "subtypes", from DataType1 to DataType10, and in case of DataType2 i have another specialization. Obviously, each of this entities have different attributes.

I think the way to go is for each subtype, to inherit the PK from Data entity, since with only TimeStamp + ID_Device cant distinguish if a row is from DataType1 or DataTypeX. That means that the supertype Data entity, doesnt become a table.

In the other hand, if i should create a table for the Data supertype, i can add a field "type" in the specialization relationship in order to uniquely identify each data row.

¿How should i proceed in this case? Since i think both options are correct, which one offers more benefits and why? Any improvement or suggestion is allowed! :D

Solution

  • Well, as no one else give an answer and im already doing the implementation, im going to answer my own question with what i finally did.

    I applied the first option i gave, which is to inherit the PK from Data entity (ID_DEVICE, TIMESTAMP) into each of the subtypes. So the supertype Data will not become a table in the db.

    The reason i did that way was because of the type of querys the user is going to do. 95% of the time, the queries will be done to 1 device and to 1 specific datatype, so it doesnt really make sense to have a Data table which include references to the WHOLE amount of rows on each datatype.

    In that case, if im asked to show all the values of DATATYPE3 generated by DEVICE 1, i should first query the general DATA table (which contains references to all other DATATYPES even i know i dont need them), and after this, i should query the DATATYPE3 table to actually recover the desired values (since the DATA table dont have values). I think this highly inneficient.

    Hope this helps someone! :)