Setup
(schema simplified for clarity)
Suppose I have a table called resource:
+---------------+--------------+------+-----+
| Field | Type | Null | Key |
+---------------+--------------+------+-----+
| id | int(11) | NO | PRI |
| name | varchar(255) | NO | |
| owner_id | int(11) | NO | |
+---------------+--------------+------+-----+
Each resource record is "owned" by a user defined in the user table:
+---------------+--------------+------+-----+
| Field | Type | Null | Key |
+---------------+--------------+------+-----+
| id | int(11) | NO | PRI |
| name | varchar(255) | NO | |
+---------------+--------------+------+-----+
A resources ownership can be transferred to other users by two different methods:
- a resource owner can request that another user takes ownership of the resource, and ownership is transferred as soon as that user accepts ownership.
- A user can request ownership from the current owner of a resource, ownership is transferred as soon as the current owner accepts the request.
In both cases if the request is denied by the approver, than nothing is done.
My naive implementation of the tables required to implement these concepts.
transferOwnershipRequest
+------------------------+--------------+------+-----+
| Field | Type | Null | Key |
+------------------------+--------------+------+-----+
| id | int(11) | NO | PRI |
| resource_id | int(11) | NO | |
| requested_by_owner_id | int(11) | NO | |
| approver_owner_id | int(11) | NO | |
| requested_new_owner_id | int(11) | NO | |
| status_id | int(11) | NO | |
+------------------------+--------------+------+-----+
transferOwnershipRequestStatus
+---------------+--------------+------+-----+
| Field | Type | Null | Key |
+---------------+--------------+------+-----+
| id | int(11) | NO | PRI |
| status | varchar(10) | NO | |
+---------------+--------------+------+-----+
// status example data
+----+----------+
| id | status |
+----+----------+
| 1 | pending |
| 2 | approved |
| 3 | denied |
+----+----------+
My business logic would update the transferOwnershipRequest.status_id as a request makes its way through the approval process.
Problem
I immediately see multiple issues with this implementation.
- There is no way to track ownership changes over time.
- Any timestamp fields in
transferOwnershipRequest(likelast_updated,request_time,approver_response_timeetc) feel cumbersome - Any kind of fallback functionality (like reverting to a previous owner if the current owner is hit by a bus) would be difficult to implement.
Question
What are my options as far as relational database design patterns for this type of situation? Have you implemented something similar to this?
Best Answer
Your request table looks fine, keep it as it is, just add a general concept of "keeping track of historical records" to your other tables, this will solve all your three additional requirements.
Start by implementing the concept of ownership in a separate table "ownership":
(and remove the
owner_idfrom resource).Now, extend this model by adding a "history functionality" to the ownership table. There are basically two standard ways to accomplish this: either you add a nullable timestamp field to this table and use the convention "timestamp=null" means "currently valid", "timestamp set" means "ownership in the past". Or, you create a shadow table "ownership_archive", with exactly the same attributes as the "ownership" table plus the timestamp field. Then you can move old ownership records which are not valid any more after approval of a request to that table.
If necessary, you can implement similar "history functionality" for your "resource" table and your "users" table, but that will be only necessary if you need to track the history of resources and users in detail as well.
You can find a broader discussion about storing historical data in a database in this older stackoverflow question.