8 ms·

Suppose you want to manage the marital status of some people. You could have one data structure, here a table in a database, where you keep (name, status) tuple

by gbog 14y ago

Suppose you want to manage the marital status of some people. You could have one data structure, here a table in a database, where you keep (name, status) tuples. This is bad, for many reason: what if someone changes name? If you need to allow undoing, how can you know which was the previous status before "maried" has been entered (widow? Single?) A better data structure here is an event table (who, did what, when), then not only you know the marital status but you know where it comes from, and can build much more on this data.

hnriot 14y ago
You're completely neglecting how the data will be used, by using an rcs like data storage of deltas you penalize the common case of wanting to query the current state efficiently. A better way would be to store a person table without name and a marital status but use it a a primary key into a detail table that has multiple rows for any individual along with dates so you have a row representing the persons state, married, name from, to. But there are also about a dozen othe better solutions than the one you describe.
- 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

hnriot 14y ago

You're completely neglecting how the data will be used, by using an rcs like data storage of deltas you penalize the common case of wanting to query the current state efficiently. A better way would be to store a person table without name and a marital status but use it a a primary key into a detail table that has multiple rows for any individual along with dates so you have a row representing the persons state, married, name from, to. But there are also about a dozen othe better solutions than the one you describe.

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.