Sql-server – complex normalisation problem

database-designnormalizationsql server

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.

Best Answer

This may not be a normalization problem. Almost every table has metadata or audit information -- the date the record was inserted and/or updated and by whom, app data such as page id, etc. This is not entity data, just other information that is needed when working with the entity data. As such, they don't really fall under the normal design rules, including normalization.

It sounds like your data concerning documents and other app info fits into this metadata role. If so, you don't have to worry about how it relates to entity data. Keep it as it is.