Databases: Meta-Data (ctime & mtime)
A RDBMS is all about data. Actually also about the relationships between them, but let's focus mostly on the datas.
We can even view a RDBMS as a glorified file system, with the tables like directories and rows like files inside. Every filesystem has some meta-data attached to its elements such as the creation date and the last modification date. With this analogy it becomes quite clear that we could also have this meta-data on the table. The good part of the filesystem meta-data design is that it's handled outside the element. The last modification date is something that isn't stored in the file itself. (Actually it could, but then it had an application meaning, and is to be handled by the application). It's also completely automated, without any intervention required, or even possible, on the application part (except for specialized tools like when we restore a backup).
Having this, knowing the last modification of an item is as easy as querying this extra information that is updated automatically by the system.
Adaptation to RDBMS is quite easy. Either the RDBMS comes already with the necessary tools to records this kind of extra information, or it is just a matter of ghosting the schema with tables that will only contain the meta-data. The information inside is updated automatically via triggers on the main table.
An example would be for the
tables (I don't use a specific SQL dialect, it's just for illustrate purposes.
Just feel free to adapt the code to your favorite database) :
TABLE ORDERS ( SERIAL ORDERS_ID PRIMARY KEY, MONEY PRICE_ADJUSTEMENT ) TABLE ORDER_ITEMS ( SERIAL ORDER_ITEMS_ID PRIMARY KEY, INTEGER ORDERS_ID FOREIGN_KEY ON ORDERS(ORDERS_ID), INTEGER PRODUCT_ID, INTEGER QUANTITY, MONEY UNIT_PRICE )
We have to create 2 ghost tables
TABLE MD_ORDERS ( INT ORDERS_ID FOREIGN_KEY ON ORDERS(ORDERS_ID), TIMESTAMP CTIME DEFAULT NOW(), TIMESTAMP MTIME DEFAULT NOW(), VARCHAR CLOGIN DEFAULT CURRENT_USER(), VARCHAR MLOGIN DEFAULT CURRENT_USER() ) TABLE ORDER_ITEMS ( INT ORDER_ITEMS_ID FOREIGN_KEY ON ORDER_ITEMS(ORDER_ITEMS_ID), TIMESTAMP CTIME DEFAULT NOW(), TIMESTAMP MTIME DEFAULT NOW(), VARCHAR CLOGIN DEFAULT CURRENT_USER(), VARCHAR MLOGIN DEFAULT CURRENT_USER() )
MTIME are obviously like
their corresponding part in file-systems.
MLOGIN are either the login used to connect to the database or for
a web application that usually have only one DB login the current application
user connected (To communicate this information to the database system, tt
could be inserted in a special temporary table at the beginning of each
request, scoped at the current connection/transaction/end-user-request/... in a
way shared by all applications).
Updating theses 2 tables is as easy as adding several TRIGGERS. (The examples given below are only for ORDERS, for ORDER_ITEMS it's the same pattern)
CREATE INSERT TRIGGER ON ORDERS o BEGIN INSERT INTO MD_ORDERS (ORDERS_ID) VALUES (o.ORDERS_ID) END CREATE UPDATE TRIGGER ON ORDERS o BEGIN UPDATE MD_ORDERS SET MTIME=NOW(), MLOGIN=CURRENT_USER() WHERE ORDERS_ID = o.ORDERS_ID END
The obvious avantage to this that you can use this technique right now on your application, since it doesn't require any applicative change. The new columns are completely transparent. Even the locking scheme is the same : if the shadow row has to be locked for updating, the underlaying base row is currently also locked anyway. The only side effect is that you will have effectively 2 times more updates in the database system, and therefore you have to be careful at where you put your new tables (in order not to be bitten by the I/O increase cost) if you have a clever table/tablespace layout.
Another advantage is that if you relax your foreign keys constraints you can even log the deletion date of a row in the underlying base table.
TABLE MD_ORDERS ( INT ORDERS_ID ON ORDERS(ORDERS_ID) INDEXED, TIMESTAMP CTIME DEFAULT NOW(), TIMESTAMP MTIME DEFAULT NOW(), TIMESTAMP DTIME DEFAULT NULL, VARCHAR CLOGIN DEFAULT CURRENT_USER(), VARCHAR MLOGIN DEFAULT CURRENT_USER(), VARCHAR DLOGIN DEFAULT NULL )
DELETE trigger is obvious, but the
has to be careful to handle the insertion of a deleted entry.
This delete feature leads us to the next entry on Databases: Version History
Edit(10/04/2009): Actually CTIME isn't the Creation Time in POSIX as I first thought, but Change time. It's related to MTIME, as the MTIME takes also the content into account. So we always have a later CTIME than MTIME (or equal). And there seem to be no Creation Time in POSIX.