Designing a model for a small business which provides multiple services which generate documents [closed]

Question

I am designing a system for a company which provides multiple services to users, all of which generate a document. The documents all have a validity period. Some of the services can be grouped, and their documents have the same length of validity. None of the services have the same parameters, except for the general ones: date of service, date of document issue, date, client data, data from who ordered the service(sometimes different than the end client).

For example, two of the services are: inspection of electrical installations and inspection of lightning protection installations. They have the same general input parameters, but they have multiple other specific parameters.

The purpose of the system is to generate these documents in a uniform way for all services, and to track their validity period so clients can be notified and arrangements be made to perform new service and subsequently generate a new document.

The company will not be adding new services for a long time period. I am not constrained by time(it's my company).

I can't make a decision which relational database model is better for this situation.

Option 1. One table for each of the services, with helper tables for parameters which should generate a table in the document, connected by foreign keys. What I don't like about this is, in order to see the validity period of all services in some kind of client view, I would have to perform a UNION query on all these tables, or perform as many queries as there are services. Performance is not an issue but it somehow seems unnatural.

Option 2. Model like in this picture:

Not a proper ER model, just prototyping for now. I think you can understand the relations from the table names.

This model would define parameters for each service in db, along with types, which would be used to generate a UI interface for it. This means I would have only one view for create, update actions. Also, this way I could perform a query on services_clients table to find all the services performed for a client(join with services), with easy filtering options. There is also an easy way to perform validity checks on the documents.

To generate a document, I would be passing service_parameter_values for a performed service to a chosen template.

But, this seems like overengineering, and a step which should have been performed when there was only 1 or 2 services, not doing it retroactively when you have 10 of them or more.

All of this seems like there should be some kind of patterns, and I fear I am reinventing the wheel.

Answer 1

I currently work in an insurance project and we also have to deal with documents to be created/printed/emailed/downloaded a lot too. The main point I learned from that is:

A document is just a report of data what actually was recorded before in the application.

The implication of this is that your main application should be designed in a way that it is able to work without a document ever being created/printed. Creating reports is just an add on to the top of your application.

So your main design should focus on the services your company performed. There I would go with a service entity that has all the overall data for the service as you already pointed out (name, validity period, etc.). But then there is another entity that keeps the details of the services that were already performed - I will name it "service performance" here. It keeps a reference to the service entity to track what kind of service this was. And it also keeps track of some general data like an order number and whatever is common to all service performances (client possibly). But all service specific data for different services just reference this service performance. So the service performance itself doesn't know about the details.

When it then comes to creating some document than that part of the application must know which data is kept alongside with a service performance for that kind of service. So the part of that application is triggered by specifying a service performance and it then compiles the report out of all the service specific data which was gathered with that service performance.

I would suggest not to model templates etc. in your application. By keeping the document creation out of your application you should be able to use standard reporting tools to create the documents you would like to have based on the database your application uses or eventually your application will provide another kind of api (like Rest). And such reporting tools should be able to manage templates etc. So you don't need to reinvent the wheel there.