Postgresql – Relational-JSON overlapping with association

jsonnormalizationpostgresqlpsql

I have a composed class called sensor that is linked to a main class Node such that:

  • class node is linked with another node to make a communication that produce an event

  • class sensor can as well detect events

So I thought of putting the sensor data inside of the table node as a JSON, but the association that allows me to detect makes doesn't apply to any schema I have seen before.

Is it a good idea to make the change that way or will that be more tricky to get the needed data?

See the full UML below; but my point concerns only the sensor.

Do you think I can convert that in R-JSON?

If you spot any place other than position that it would be suited to use R-JSON, I would like to think of the option.

http://www.plantuml.com/plantuml/png/TLPjRzis4FukGF_Xe7vOqyP1tNLRAWn3FTS3WYrfa6RzKXG2JLri8XBfKNGM8_D_t_7Eb2rd2vnOF3wyUtWlZ_AX2yg7TTlaMPvz1zUJcydbx7PsypdFkdjZLygh5XfZCMnM22lNREBYFpUT3qiNtUHPCA51g5LGC5STmWCkZLwJAJWmziwhBgmz0dnvUn7XAwNlrGAXcBfsvJfbGm4lpd-QpcQ_mbEUgNaNlD81TAEw3gvSZT4Cy5hLTTMXxPn_UG8bF3XJivtWjhj3dpQlngKydLwztJsTb20Fm2TgR7fFfVZPvbaCLOmBU3KQGV7cxEpLwPZ-5Ft1F9io9J489rfuevhAhbkYP6mGQqkk6ZBGLOnT0BlFCxdHXQJ4hpMdHVoO6g2au_2qjkQlDH8UuYzzKhvQDpSDLZfb3LwyE__-upadJJn_IOdV-MnRHV79ee9nTiyH5-WPqIfFiGvvHkCnzvXwxshY6xriK7dQfN9Zt1AUmfG-GkJI1SSSuBLMnD-4pI3ojaV6DDmBT8-jMlNuFtXDmPyudaJTcojgLwFbxjlXpftns2hBORRIiupWtYguo40bfzAp9SzUnkzJNft0sHbGfxPK5yrv77RmtavT_6xyu_lPc2b8z05WLvOuFh6A1nib3DRLBK648yQYFTyKLyvY5LpbB1wiwA8RFYljCvWuV42Uje_yCvYs7M569AAcHHACTWLbgZRrQKK04_2eaxIZnK7xGXdmCJ09laBIX2hUdjr_lfvDBoP_LDF9JLpUtlmv_Jq-N7ww_HI_sRvDhQsqDlMUsiAh1sgK8GVhsZcfG2y_krqlp0SCliQYVuiYScYj1dFwDwgm97-gGP_YIdhIR4YMi5sXLppB1tma--A9nb1KeiQ0kc-5L20GVUW7lMYSLetfKbVzyDlxsUJDViolNMSE-gbnTc72kexXxngdH8BYp1yQXneaVKcQB2wiQd5uAvAOG8IrguUt8ZVfR9oNqQYWsUa9bG1pZ7owgFsYg157i77HemY6ZwOB7Vdb530nnlxy3sdliK8EzdgDexSIwx3GWCWZLKVI725YuPEfWmR6GOq7Eyzh5adsqHb3sf9dHsH7NvUcfZZVHh_iNXGoWyzFHv4yjh4S9XxnIYFHfPJp4fnU-rtY6CnlAiumeJLByz8rTPVgb2IcVx6DTtagOSMYZiBj0zgQ_Z7u5m00

Best Answer

If you want simple and efficient SQL statements, don't model an entity as a JSON column. Create a regular normalized table that expresses the functional dependency between the sensor's attributes explicitly.

JSON in the database is a good thing and has its applications, but modeling foreign key relationships like the one to event is not among them.