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.
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.
With Rails-like conventions, here's a minimalist Type II SCD definition:
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