1

I have the following SQL Table:

Table: dbo.Document
_________________________________________________
docID           bigint              (PK: required)
docTypeID       bigint              (required)
UploadName      varchar(500)        (required)
FileSize        bigint              (required)
DateUploaded    datetimeoffset(7)   (required)
tbl_userID      bigint              (nullable)
tbl_orgID       bigint              (nullable)
tbl_clientID    bigint              (nullable)
tbl_contactID   bigint              (nullable)
tbl_transactID  bigint              (nullable)

This Table stores uploaded documents. Documents can be uploaded for various entities:

  1. Users
  2. Organizations
  3. Clients
  4. Contacts
  5. Transactions

This one table stores those uploaded documents and links it to the relevant entity. It structure allows me to add the ability to upload documents to other table later on by just adding a foreign key to the Document table.

I made all the foreign keys "sparse" columns to save on space as only 1 of those columns will have a value.

Important rule: Must link to 1 table exactly. Cannot link to no table, cannot link to more than 1 table.

How do I ensure this at the database level. I was thinking of using a check constraint - but the syntax of that might get complex (what if we uploaded documents for 20 different tables).

Can someone recommend a solution please.

Mohamed Cassim
  • 153
  • 1
  • 10
  • how about having 1 link id instead of many and 1 column to describe what to links to ? Put index on this column and it should be possible to join against the other tables with good performance – t-clausen.dk Feb 18 '15 at 14:54
  • If you upload documents for 20 different tables, ANY solution that enforces RI is going to be complex. I would use a check constraint with a UDF. – Tab Alleman Feb 18 '15 at 15:31

0 Answers0