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?

Re: storing documents in database, centralize in one table? 80 80

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.

Re: storing documents in database, centralize in one table? 80 80

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.