Design of DB which stores files and has a history.

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.

Option 1.

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..

September 20th, 2014 11:14am

Option 2.

Split ATTACHMENTS table into 2 tables (ATTACHMENTS_META and ATTACHMENTS_DATA) to store the file in a separate table. Files will never be deleted. As a result, we can safely remove ATTACMENT column from ATTACHMENTS_HIST table. We dont need it anymore because ATTACHMENT_ID will always point to the file:

Here ATTACHMENTS_DATA table has FK:

CONSTRAINT FK_ATTACHMENTS_DATA__ATTACHMENTS_META FOREIGN KEY (ATTACHMENT_ID)

            REFERENCES dbo.ATTACHMENTS_META (ATTACHMENT_ID)     

Because of this, the relationship between ATTACHMENTS_META table and ATTACHMENTS_DATA table is 1:1.

At first blush the idea possesses common sense, but on closer examination it fails because we cannot delete a row from ATTACHMENTS_META. Why? Because it is a Master table and ATTACHMENTS_DATA is a Details table. We cannot leave the detail without master. (The data in the ATTACHMENTS_DATA never deleted.)

What if we swap Master and Details table? I mean if ATTACHMENTS_DATA is Master and ATTACHMENTS_META is Details. Well, in this case we can delete a row from ATTACHMENTS_META table and leave the corresponding file in the ATTACHMENTS_DATA. So it seems that it solves the problem.

However, it raises another issue. Do you remember that instead of a file we can have URL? URL stored in the ATTACHMENTS_META table which is Details table in this case. And we cannot create a row in that table until we create a row in the Master table. But we dont need a file for URL attachment. So this solution will not work.

Eventually, option 2 has the following solution:


Here ATTACHMENT_DATA table is Master and ATTACHMENT_META table is Details. Optional (null-able)   ATTACHMENT_DATA_ID column was added to ATTACHMENT_META table. It allows splitting PK and FK in this table. 

Now the question: Which option (1 or 2) would you prefer? Can you suggest option 3?

Option 1

+ There are only 3 tables

- File is copied from ATTACHMENTS to ATTACHMENTS_HIST table before deleting from ATTACHMENTS.

Option 2

+ File is not copied.

- There are 4 tables.

- Relationship between ATTACHMENTS_DATA table and ATTACHMENTS_META table is 1:n which actually does not reflect the reality.

Thank you in advance.

Free Windows Admin Tool Kit Click here and download it now
September 20th, 2014 11:18am

Hi Alexey,

From my point of view, if you need to track the DML (INSERT, UPDATE and DELETE) changes in the ATTACHMENTS table, you dont have to create the History table. You can use the built-in Change Tracking feature which is introduced from SQL Server 2008.

To enable Change Tracking for the table, we need to first enable it at a database level. So once we have enabled it on a database level, then we can individually choose which tables we want to be tracked and enable these tables. For more details, please review the following blogs.

SQL Server Change Tracking on Table (Without Triggers): http://www.codeproject.com/Articles/338183/SQL-Server-Change-Tracking-on-Table-Without-Trigge
Using Change Tracking in SQL Server 2008: http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/

Thanks,
Lydia Zhang

September 22nd, 2014 1:12pm

Thank you Lydia for your option 3 - using Change-Tracking feature. Definitely it should be considered.

I would also like to know your opinion regarding options 1 and 2.

Thank you in advance.

Free Windows Admin Tool Kit Click here and download it now
September 22nd, 2014 8:44pm

Personally, I perefer option1 as it more fit for your scenario. And less tables are easy to manage in SQL.
September 27th, 2014 8:39am

The problem with both options is being expressed by: "we dont need a file for URL attachment"

The current designs are lumping together files and URLs within the same set. Being in the same set implies both URLs and Files have the identical attributes (such as attachments). And yet above quote clearly states the attributes of files and URLs are different. You need to consider designing an separate URL table to hold a URL's attributes, and you need to consider designing a separate file table to hold a file's attributes. That design will be more complex, but so too is the logic being expressed in what I quoted :).
Free Windows Admin Tool Kit Click here and download it now
September 28th, 2014 7:39pm

You need to consider designing an separate URL table to hold a URL's attributes, and you need to consider designing a separate file table to hold a file's a
September 30th, 2014 9:36pm

Can you post the table DDL (CREATE TABLEs)?  Thanks.
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2014 3:06pm

Can you post the table DDL (CREATE TABLEs)?&nbs
March 9th, 2015 12:40am

Option 1 looks OK.
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2015 12:12pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics