I am transfering a managment application from Access to SQL Server and from vba to .net. Still in study and learning phase. I am wondering if one of the solutions I developped previously can not be improved using a more normalised technique but could not find out if and how to improve.
I have a unique centralised documents table (and conected form) containing a row for every document (word, pdf or image) refered to by the application with besides document specific data it contains two columns referring to the application's element linked to that document = where it was added like Bank statement, invoice, budget, relation, contact, Project, Investment, Account with as purpose allowing a shortcut to the origin of the document i.e. enabling to open directly the form from where the document was originally added to the database.
I did it dedicating two columns one (a one char FK) "Origin" refers to the PK of a table containing the data to access the table or open the form where the document was originaly created the other "OriginID" contains the Origin table Primary Key (they can all be refered to through a unique integer clustered primary key).
That way of working although efficient does not comply with normalisation but I did not find a way to normalise those relations as there are 9 entities that can create a document record.
Now that I am reviewing and rewriting the application for SQL server I wonder if there could be a better way to handle those relations.
invoice_document (document_id PK, invoice_id),budget_document (document_id PK, budget_id), and so on. However, without additional constraints this will allow multiple sources to be recorded for a single document.