Dear all,

We are starting a new project and I am looking for some guidance / best practice of how to implement Haystack in a relational database.

One option would be to have one large metadata table based on the grid format: rows are entities, columns are tag keys, cells are tag values. Time-series data would be stored in separate tables.

But this doesn't seem like a good database design. Is it better to have separate tables for site, floor, room, zone, equip and point entities?

Can you point me to some other DB implementation? I searched the website/forum but no luck.

Thanks, Karol

I have not actually done it, but I can give you some thoughts...

1. Many people use Haystack in Excel so looking at how people use Haystack with Excel can provide lots of ideas about how to flatten into 2 dimensional tables
2. Haystack in tables will often be very sparse with lots of empty cells
3. Most of the sparse cells are marker tags since most tags are markers; for example most points will have the same core set of tags such as kind, unit, siteRef, equipRef, but there will often be dozens of different marker tags used
4. If you do map tags directly to columns, then you should probably expect to be adding columns a lot (schema changes)
5. If you plan to make your system a true Haystack server and/or you want to query with Haystack filters, then it will be easier to map filters to an SQL expression if everything is one table
6. Using one table will probably also be easier to map Refs and ids to an auto-generated primary key
7. But also consider Haystack is probably more RDF like than SQL like, and RDF databases tend to store data as triples of subject, predicate, value (which in our case would be entity id, tag name, tag value)
8. If your application is mostly accessing the data via SQL, then having separate tables would probably be more natural

Hi Brian,

Thank you for your advice. We have decided to go with the more standard DB design with multiple tables. So we have the standard hierarchy of site->floor->zone->room->equip->point.

The only complication is that we had to attach weatherPoint to the rooms, because we consider solar radiation for the different facades.

Best regards,

Karol