5

So this is all hypothetical and theoretical really. This isn't a real system I'd have to architect, but its a good question.

A company stores and reproduces contracts. Contracts can have many documents associated with them. There are up to 200 types of these documents that can be assigned to any single contract.

That's basically it in a nutshell. I don't want to write 200 classes and have 200 sql server tables in reference to each document type. Is there a more efficient solution to collect the contracts/documents in a database and then classify them in a C# web app to display?

Improve this question
7
  • 2
    What about storing all documents in one sql table, with a foreign key to a document type table (or simply an enum to specify type)?
    – Niklas H
    Dec 22, 2016 at 23:01
  • @NiklasH are you saying in C# have something like a auto enum generator for the document type table? Then having one Contract class and one Document class that simply uses the enum?
    – tshoemake
    Dec 22, 2016 at 23:04
  • The essemtial part you did not mention here is how if those 200 types of documents (or the part of them you need to store in the db) are similar structured (so your "200 sql server tables" would all have the same columns), or if each of them has a different structure (so it would get different columns).
    – Doc Brown
    Dec 23, 2016 at 5:36
  • @DocBrown thats a very good point. I think the solutions provided would be if the docs were, in fact, similarly structured in nature. However, if they weren't........ that adds another level of complexity. Whats your take on it?
    – tshoemake
    Dec 23, 2016 at 5:44
  • 1
    ... So if you really want to learn more about modeling in a generic or not-so-generic fashion, I recommend you read Fowler's "Analysis patterns". This is not specificially about how to model documents, but I am sure you will find the modeling techniques you are looking for inside that book.
    – Doc Brown
    Dec 23, 2016 at 6:34

2 Answers 2

Reset to default
4

As Niklas H wrote in a comment while I was typing this:

You have one table for contracts.

Then you have a table for documents including a foreign key to the contracts tables for the contract the document belongs to.

You then have a document type table where your document types are. From your document table you have a foreign key to the document type table, so that you can store what type a document is.

This will allow you to create new document types without having to create new classes. These document types will not be enums.

You will need a dialog in your system where you can create new document types, they'll probably have a name and a description.

When you enter a new document you select the document type from the list of existing document types.

Improve this answer
1

One could just store the document itself in the database and not bother with any other tables around types etc. Most popular document systems have properties and meta data associated with it, so use that instead of a predefined system of tables.

You question is kind of broad, but let's assume that all the documents will be stored in the database as PDFs. Even if they weren't the process would work the same. One could have a standard set properties/meta data to the document and then read that data from the document itself (PDF) instead of the table(s).

If one needed to search or query, then extracting that information and putting it into a table so that one can write queries against it would be useful as mentioned by @Bent and others.

You could even go a step further and as the document is uploaded into the database your process examines the document meta data, stores important pieces of that data in a table for future reference, and saves the document and data. You wouldn't need any sort of reference tables because as users are uploading documents the meta data for the document is being used to classify and tag the document. That way new types are automatically added without the need for maintaining any of that reference data.

So, one table for Contract. One table for Documents. A many to many Contract/Document if documents can be shared across contracts. if not just go with the FK as mentioned by @Bent.

The document table could have the important properties as well or they could be separated out into separate reference tables.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.