Timeline for Approaching database design
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 19, 2020 at 20:50 | comment | added | Gerard H. Pille | jsonb arrays are the only "workaround", but I doubt if they can be performant with serious amounts of data. | |
| Mar 19, 2020 at 20:16 | comment | added | Rick James | MySQL/MariaDB has no 'reasonable' way of storing an array. | |
| Mar 19, 2020 at 10:22 | comment | added | Gerard H. Pille | @RickJames, what version of PostgreSQL ? | |
| Feb 24, 2020 at 16:39 | vote | accept | Ajility | ||
| Feb 24, 2020 at 16:39 | comment | added | Ajility | I misunderstood. This is a very clever approach. Thank you for answering Gerald. | |
| Feb 24, 2020 at 15:55 | comment | added | Gerard H. Pille | I thought "dim_id" was the sensor's identification. The 86400 sensorvalue elements count for each second of dim_date. Have a look at dbfiddle.uk/… . In the second select I sum the values of the first minute of the second hour. | |
| Feb 24, 2020 at 13:42 | comment | added | Ajility | What does selecting a specific index from the array look like? Each sensor has a unique name is what I’m getting at. The data has to be accessible for generating reports, we may just need to look at just a few sensors over a time period. Linking a positional index to the name creates room for error for people who follow up on my code. | |
| Feb 24, 2020 at 13:07 | history | edited | Gerard H. Pille | CC BY-SA 4.0 | added 150 characters in body |
| Feb 24, 2020 at 13:06 | comment | added | Gerard H. Pille | I have no idea what "row[33]" has to do with a "sensor 30 value". The layout of the table could be as simple as see edit. | |
| Feb 24, 2020 at 12:51 | comment | added | Ajility | Not at all. However arrays are index based, i.e. row[33] corresponding to sensor 30 value in my example. I see a potential for code readability issues. | |
| Feb 24, 2020 at 12:46 | comment | added | Gerard H. Pille | Do you consider the use of an SQL select statement complex? | |
| Feb 23, 2020 at 2:42 | comment | added | Ajility | This would solve the storage issue but I think it would call for complex code when running queries on a specific sensor over a specific time period. | |
| Feb 22, 2020 at 19:20 | history | answered | Gerard H. Pille | CC BY-SA 4.0 |