4 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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 questionolder stackoverflow question.

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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.

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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.

3 deleted 4 characters in body
source | link

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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 "database lifetime"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.

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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 "database lifetime" of resources and users in detail.

You can find a broader discussion about storing historical data in a database in this older stackoverflow question.

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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.

2 added 406 characters in body
source | link

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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 the samesimilar "history functionality" for your "resource" table and your "users" table, but that will be only necessary if you need to track the "database lifetime" of resources and users in detail.

You can find a broader discussion about storing historical data in a database in this older stackoverflow question.

Start by implementing the concept of ownership in a separate table "ownership":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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 the same "history functionality" for your "resource" table and your "users" table, but that will be only necessary if you need to track the "database lifetime" of resources and users in detail.

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":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from 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 "database lifetime" of resources and users in detail.

You can find a broader discussion about storing historical data in a database in this older stackoverflow question.

1
source | link