Concerns about reference documents

[sse450]

I have never used SL for uploading documents to the database.

Seeing a lot of nice improvements for reference documents in the last version (3.2.12.42), I wanted to give it a go and uploaded some documents. I see that they are stored in archivedata table. Each document comprises many many text lines in archivedata.

I need some insights for the performance of database when uploaded documents reach hundreds. Is it a viable way of storing documents?

Please advise.

[Tekki]

You can store terabytes of data in PostgreSQL if your server is big enough. This shouldn't affect the performance. We could wonder why the files are stored as uuencoded strings and not as binary, which increases the volume by around one third. But that’s how things work for the moment.

The more important question is if it makes more sense to store the documents inside SQL-Ledger or externally.

In most of the cases we and our customers store the documents externally. This means we create codes with a URL shortener and add them to the transactions. Like that it is possible to make some funny stuff, like add a first entry that links to a PDF datasheet on SharePoint, a second one that links to the web shop of the supplier, and a third one that links to the warranty portal of the manufacturer.
The drawback is that this creates a complicated network of systems that eventually breaks.

If on the other hand you use the reference documents for business records, you may have to consider the legal requirements. In our country we have the obligation to preserve the records for 10 years. In this case you’re better off if everything is stored in one place. If you have a tax audit, you just need a running SQL-Ledger to make all your documents available.

There are still some technical questions. To reduce the amount of data we definitely need deduplication. And then you probably don’t want do have all the archivedata included in every backup, because in most of the cases this table just grows, whereas the rest of the data changes. These are things that have to be solved in the future.

«And now a word from our sponsor»: I don’t want to forget to mention that most of the development of version 42 was sponsored by the company of @bentf4y. 👏

[sse450]

I like how SL stores images. They are in images directory on the file system. Many leading DMS systems store the files on disk. Simple and clean. Easy to recover in case of database corruption.

Is there a specific reason for using different store systems for images and documents in SL?

Also, I would like to thank @bentf4y sincerely for their kind support.

[Tekki]

I like how SL stores images. They are in images directory on the file system. Many leading DMS systems store the files on disk. Simple and clean. Easy to recover in case of database corruption.

In case of a database corruption you need a database backup. In case of a file system corruption you need a file backup. This means you need two different systems to backup the whole installation.
Of course it is possible to store the documents in the file system. But I would be surprised if any serious DMS stores the files under their real name, so that a second with the same name destroys the first one. And if it makes all the documents directly accessible to the whole world.

Is there a specific reason for using different store systems for images and documents in SL?

I can't answer this question because it's not my invention. I would probably have solved it differently.
But still, to store everything in the database has some advantages. If your server is used by several of your customers, how can you ensure that each of them gets a reliable backup of all his data? If everything is in the database, he can download it directly from the menu. If the data is spread over the server, things get difficult. It's the same for users with a local installation in their office.

[Tekki]

By the way, it wouldn't be too difficult to support different types of storage.