We have a very simple requirement: Object 'A' might have several attachments. Attachment means either a physical file stored in the DB (max 10 MB) or URL pointing to a file in the web/network/anywhere. In case an attachment is a file, that file cannot be updated. Attachments might be deleted.
We started with the following simple DB schema:
Then we realized that we need to have a History table for the attachments to track changes in the FILE_NAME and URL columns. (The file stored in the ATTACHMENT column cannot be changed, but its name can be changed). The whole row in the attachments table might be deleted. So the initial thought was to create this table:
However, this design has one drawback the same file is duplicated (stored in several places). Since the file once inserted, can never be changed, there is no point to store it in the history table every time its name was changed.
My idea is to leave this schema unchanged, but to change the logic a bit. Every update in the ATTACHMENTS table should trigger copying only of ATTACHMENT_PID, TABLE_A_ID, FILE_NAME and URL fields from that table to ATTACHMENTS_HIST. The ATTCHMENT field is not copied. When the row is deleted from ATTACHMENTS table, then all fields including the file itself are copied to the ATTACHMENTS_HIST table. As a result of this, we store the file only in one place.
See option 2 in the next message..