4 ms·

What you describe is just a denormalization of my solution. You are already optimizing a proposal that was designed as a very short example of a better data str

by gbog 14y ago

What you describe is just a denormalization of my solution. You are already optimizing a proposal that was designed as a very short example of a better data structure. That's absurd. If you need to access often the current marital status you can cache it, store it on the client, use a materialized view, write it in a file along with other info, etc. I would advice personally against the plain denormalization you propose (if I understood it well), because it means your application logic will have to handle it, and your data structure will not produce a very simple straight forward code that is the appendage of good data structure.

lloeki 14y ago
If you want to keep marital status history, then the marital status is a SCD [0] With Rails-like conventions, here's a minimalist Type II SCD definition: +--------+ +-----------------+ +----------+ | people | -> | people_statuses | <- | statuses | +--------+ +-----------------+ +----------+ | - id | | - id | | - id | | - name | | - person_id | | - label | +--------+ | - status_id | +----------+ | - until | +-----------------+ where person_statuses.until is the last date where this relationship is valid. The logic follows from the data structure definition in a natural manner. 'WHERE person_statuses.until IS NULL' will immediately give you the last status of someone/everyone. You can trivially update one's status by UPDATEing 'until' and INSERTing a new record, wrapped in a transaction. Additionally, you can use until as a guard WHERE clause for such an update to implement a form of optimistic locking. You can also add a column relating a person to another. With a slightly more complex query you can easily make the relation symmetric and remove the need for 'duplicate' reciprocal records. Handling name changes and preserving navigable history in the people table is not much harder. The wikipedia page about SCDs gives interesting cases. [0] http://en.wikipedia.org/wiki/Slowly_changing_dimension http://en.wikipedia.org/wiki/Slowly_changing_dimension
- gbog 14y ago
  That's interesting, thanks. I still wonder if a simpler event table is not a better data structure, mostly because it is a read write only structure, no need for updates. Granted, getting the current status is a bit slower but keeping pointers to the latest event plus chaining events can fix it.

by gbog 14y ago

lloeki 14y ago

If you want to keep marital status history, then the marital status is a SCD [0] With Rails-like conventions, here's a minimalist Type II SCD definition: +--------+ +-----------------+ +----------+ | people | -> | people_statuses | <- | statuses | +--------+ +-----------------+ +----------+ | - id | | - id | | - id | | - name | | - person_id | | - label | +--------+ | - status_id | +----------+ | - until | +-----------------+ where person_statuses.until is the last date where this relationship is valid. The logic follows from the data structure definition in a natural manner. 'WHERE person_statuses.until IS NULL' will immediately give you the last status of someone/everyone. You can trivially update one's status by UPDATEing 'until' and INSERTing a new record, wrapped in a transaction. Additionally, you can use until as a guard WHERE clause for such an update to implement a form of optimistic locking. You can also add a column relating a person to another. With a slightly more complex query you can easily make the relation symmetric and remove the need for 'duplicate' reciprocal records. Handling name changes and preserving navigable history in the people table is not much harder. The wikipedia page about SCDs gives interesting cases. [0] http://en.wikipedia.org/wiki/Slowly_changing_dimension http://en.wikipedia.org/wiki/Slowly_changing_dimension

gbog 14y ago
That's interesting, thanks. I still wonder if a simpler event table is not a better data structure, mostly because it is a read write only structure, no need for updates. Granted, getting the current status is a bit slower but keeping pointers to the latest event plus chaining events can fix it.