I've been reading Snodgrass's "Developing Time-Oriented Database Applications in SQL" and I'm consulting other documents as well, but I'm willing to find more modern stuff about time-oriented design for databases and the tools available for that in Postgresql.
Also found this document.
I would be grateful just with any link or advice on the topic discussing:
- Temporal and bitemporal tables
- Validity, applicability time, transaction time
- Primary keys, constraints and referential integrity in temporal DBs
- Design and good design practices for temporal DBs
- Native Postgresql solutions for all of the above
I have still more to read from the book and I've discovered some SQL:2011 issues about time, but they'are not implemented in Postgresql.
There's still some things I don't know how to handle or understand, for example, I found this article mentioning this:
CREATE TABLE travel_log ( id serial PRIMARY KEY, name varchar(255), travel_range daterange, EXCLUDE USING gist (travel_range WITH &&) );
which I understand it could be useful for not having overlapping rows, but even though I searched the Postgresql docs, I only read what the EXCLUDE does and that gist is just some type of INDEX, but I can't figure out which kind of syntax is the "WITH &&" thing and what does it provide.
I would be happy just having resources where I could learn about all this. Thank you very much.