I currently have openhab2 set up with mariadb. Every device has it’s own table with a timestamp and value. Is it possible to get every device to write to the same table with a foreign key to another table for devices?
The problem is, that different devices may have different value types (int, float, string, …). So they can’t be written (in their native type) to the same column in a table.
What would be the benefit writing everything to the same table?
Why would you need or want all values for all items to be written to one single table?
It’s a school project and it required a erd for the design. It seemed to make sense to have all the readings in one table and it relates to devices table. There are over 100 devices to read from. I imagined that would be redundant to have a table for each device. Most of them are from a ion meter and has int values. Can I have it write to one table and have different columns for different types?
The short answer is no. The Persistence engine will create a separate table for each Item.
If you have the choice, it might be better to chose a more appropriate database like InfluxDB or MongoDB where the concept of “table” doesn’t exist. Given that OH generates timer series data, a time series database like InfluxDB is probably most appropriate. But given this is a class project and what is most appropriate rarely comes into consideration…
One thing you will probably learn over time is that it changing the actual implementation of something to make the documentation easier is rarely going to result in a good solution. As Manuel and Rolf indicate, there are technical reasons why you should have each Item in it’s own table. The amount of work you are required to do to create an ERD is not one of the considerations.
The database doesn’t care how many tables there are. It makes sense logically since each Item is a separate “thing”.
It’s more efficient for all of the OH database operations if the Items are in separate tables. ALL OH database operations are done on a per Item basis. So when one calls MyItem.previousState in a Rule means if all Items are in the same Table a simple “select the most recent entry in table x”. If all the Items are stored in the same Table this becomes “select the most recent entry in table y where the Item column is x”. The latter is less efficient and the error cases are expensive (e.g. what if there is no data for that Item in the database?).
Absolutely not. Each column in a relational database has a type. The type is fixed.