Separate table for image elements with image field

Question

I store different elements (notes, articles, figures, files) in one table (for all types of elements there is a lot of metadata - for example, categories, tags, rating, statistics, etc.).

My first construct was this: table Items , as well as another “details” table for each of the item types ( NoteItems , ArticleItems , PictureItems , etc.). To get one item, tables must be joined together (SELECT * FROM Items INNER JOIN PictureItems ON Items.Id = PictureItems.Id WHERE Items.Id = N).

I am sure that this “book-like” design will work beautifully (done several times), however, I begin to wonder if the design is too high. It would be much easier to have one table ( Elements ).

Say that there are about 5% of the elements of an image or file type.

And now, the question is: if I go for a (almost) single table design, would it be better to have detail tables for image fields anyway (for images and files, of course)?

Scenario 1: only one table: Elements (for storing notes, articles, images, files ...)

Scenario 2: two tables: Elements (for storing notes, articles, image files), ImageItems (for storing only the image field of types of image elements, file); one-to-one relationship

(Scenario 3 will be a minor change to Scenario 2, with 3 tables (Items, PictureItems, FileItems))

Scenario 1 Benefits:

• simpler queries (no connections)
• no transactions (only one table is updated in INSERT / UPDATE)
• performance, scalability due to lack of update transactions

Scenario 2 Benefits:

• cleaner design
• lower data consumption (in scenario 1, about 95% of elements of the type other than the image or file will be NULL in the image field, then about 16 bytes will be spent for the pointer)

Which scenario will you choose: 1 (no transaction) or 2 (lower data consumption)? Thank you for your opinion.

Answer 1

If programmers are smart enough to query only the right columns from a table instead of “SELECT *,” the first design approach looks fine.

You need to take care of indexing, link restrictions, etc. for the second project.

Answer 2

If the database does not need to know what is in these elements (it will not be indexed or searched on them), then option 1 seems to be the best option (if you have only one "Item" column in the form of a BLOB) - you can simply read the elements as binary data and process it on its own, avoiding this internal connection.

I don’t think that scenario 2 gives you lower data consumption - you can just use the BLOB field (and in any case, the overhead of the extra ImageItems table is probably comparable to 16 bytes per line)

So, I personally would go for option 1, but, of course, it depends on how you process the elements when they exit the database.

Answer 3

The first approach is usually punished if you use some kind of ORM or automatically generate your DAL (SubSonic?). You will return an Image column (and its data) every time you walk around a DAL object (or collection) so normally I would use script 2 (or 3)

From an SQL point of view, both scripts will work approximately the same depending on your storage engine (ISAM, InnoDB, etc.), but even there the advantages and differences between the scripts are negligible.

Answer 4

If you are right in about 5% of your row, which actually has additional images / binary data, then I would definitely say that use the single-table approach in combination with the Murthy hint - do not forget to make SELECT * in this table, but only query the columns that you really need - leave BLOB columns as often as possible.

If your database is growing, you can also check out a separate filegroup for BLOB data so that things are split and clean (but this is only true when you are dealing with hundreds of thousands of lines or more, and if you can split filegroups into several individual drives).

KISS - Keep it smart and simple - whenever possible !:-)

Mark