storing documents in database, centralize in one table?

[zensys]

I have a database oriented at clients and services provided. In several tables I would like to store documents, e.g. under clients a copy of passport and under companies a copy of the articles of association, mostly pdf's and doc's.

Currently I have the documents stored in the respective tables directly at the user record which helps me separate confidential user information. Information on the files I can all extract from the contents and filename depends on the record where it is stored.

But now I need to add friendly column names to the (medium)blob fields. I see two ways (apart from solving it in my php-application):

1. Adding a table with column_name, friendly_column_name or 2. moving all files to a dedicated files table and a file_type table with the possibility of storing additional information on the files and file_types like the friendly column names.

Disadvantage would be the additional overhead of linking files to tables and users and from a security point of view the risk to have a user view other users documents.

Is any of these possibilities best practice or is it up to my preference?

[griswolf]

If you move blobs into their own table, the database may be able to optimize access to the primary table(s) because the record size is fixed; and for that matter, the blobs table(s) may also work a little better because they can be optimized for that kind of record.

If you have security concerns with a new blob table, how is your concern any better with the current system? Unless you are worried about bugs in your PHP, I suppose. A JOINed SELECT is every bit as safe and transactional as a single-table SELECT.

The overhead of using a JOIN can be very small if the foreign key(s) are indexed.

One option to keep things "simple" and similar to current is to add a friendly_names table that maps column names to friendly names; and read that in only at startup, then your PHP can use that information to present the friendly names when data is accessed.

[zensys]

Hi Griswolf,

Thanks for your reply which brings some new insights to me, like the optimisation. And as to security I realise that I am worried about bugs indeed. The simple option you mention is what I was tempted to do but now I think I will do it the "right" way. The only thing which intuitively does not feel right to me is that for each document I need to add a owner-id which is the id of the table the document belongs to. So this id has a different meaning depending on the context of the document type (which in the table "document_type" is linked to a table). But I guess that is just a feeling.