CODEWITHSUNDEEP
databasesqlite
jacksons123
jacksons123

Reputation: 181

Best Way to Relate a File to a Database

So, for example let's say I wanted to setup a SQLite database that contains some data on invoices. Let's say each invoice has a date, invoice number, and company associated with it for simplicity. Is there a good way for the database to be able to access or store a PDF file(~300-700kb/file) of the specified invoice? If this wouldn't work any alternative ideas on what might work well?

Any help is greatly appreciated

Upvotes: 0

Views: 35

Answers (2)

MikeT
MikeT

Reputation: 57013

You could store the data (each file) as a BLOB which is a byte array/stream so the file could basically be stored as it is within a BLOB.

However, it may be more efficient (see linked article) to just store the path to the file, or perhaps just the file name (depending upon standards) and then use that to retrieve and view the invoice.

Up to around 100k it can be more efficient to store files as BLOB. You may find this a useful document to read SQLite 35% Faster Than The Filesystem

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

SQLite does support a BLOB data type, which stores data exactly as it is entered. From the documentation:

The current implementation will only support a string or BLOB length up to 231-1 or 2147483647

This limit is much larger than your expected need of 300-700 KB per file, so what you want should be possible. The other thing to consider is the size of your database. Unless you expect to have well north of around 100 TB, then the database size limit also should not pose a problem.

Upvotes: 2

Related Questions