4

I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like:

Table name: tabla

Fields:
  idtabla not null,
  text (varchar(n)) not null,
  image (mediumblob) {this can be null}

The doubt: Is better to create another table and when be necessary to use the image, query the new table? Why is your solution, better for design? MySQL's time response, ease to make queries, etc?

  • Do you need to check image existence always? Do you always need to get the image if it exists? Do you expect to have thousands of rows or millions? – jkavalik Jun 12 '15 at 5:37
  • 1
    Check this queestion it is a similar situation – Masoud Jun 12 '15 at 7:36
  • @user1786423 I always need to check the image, in case of existence. It's something like: If image exists then loadImage(). It's expected around few thousands rows. – andreshg112 Jun 12 '15 at 17:14
  • @Human few thousands row is no concern from performance viewpoint and if you have to check it always, then there is nothing to gain from separating it. – jkavalik Jun 12 '15 at 19:12
  • Ok. However, I decided to use another table because after analyze the situation, not always I will check image, I will use a field in the original table to know if the row has image in the other table. In all ways, thanks. It's something to have present for other situations. – andreshg112 Jun 13 '15 at 0:06
3

You are touching on an almost philosophical argument here: should NULLable values be allowed at all given they violate the "closed world" assumption of the model relational databases are derived from (see relevant sections of http://en.wikipedia.org/wiki/Null_(SQL) and a number of other questions here such as Why shouldn't we allow NULLs? for more notes on that). To avoid unknown values splitting properties that might be unknown (or simply not applicable in all circumstances) into their own relation (table) so that you don't have values that are unknown but instead if a value is unknown it simply doesn't exist. While this satisfies the theory, there are practical implications that make it less desirable:

  • A JOIN operation is not free in most RDBMSs, searching for the properties in the extra table(s) increases the work the engine needs to do to satisfy your queries.
  • If the existence of properties is not codependent (i.e. each proerty can be unknown irrespective of the others) then to carry this implementation to its conclusion you sometimes end up needing a table for each property.
  • The extra joins add to the complexity of your queries and can reduce maintainability.
  • Updates also become less intuitive in places: blanking a property now becomes a DELETE operation and updating one may be either an INSERT or an UPDATE.

Of course there are circumstances where that first point is reversed and breaking properties off improves efficiency:

  • Most RBDMSs use page based storage and breaking optional information off from core data means you can fit more core data rows in a given page. Depending on your data size, RAM, and storage infrastructure this could be significant in reducing the amount of IO required for some large queries (but often isn't: try not to "over optimise" around this point, at least without running good benchmarks to make sure things are improving not worsening).
    This is of course assuming that your queries are designed so they pick up only what they need so the engine won't need to care about the drawing in the extra information anyway.
  • Similarly there is less writing to do if you are only updating core information in an INSERT or UPDATE, and if you have constraints or triggers around those extra properties this processing is potentially avoided on each row change.

Your example looks like it is storing images (or at least large bits of data in blob type columns. There are two extra considerations here:

  • Most database engines store large data "off page" anyway, so if you avoid SELECT * you get the rows-per-page bonuses "for free" anyway.
  • Does moving them elsewhere allow you to share the large data between multiple entities rather than storing the same blob more than once, or is the information (if present) unique to a given row?

tl;dr: So no hard and fast answer I'm afraid. My advice would be to do whatever best fits your conceptual model of your data and therefore requires less thought on your part to maintain. This usually, but not always, means using NULLable columns rather than separate tables. Unless your data is truly massive the performance differences are going to be negligible.

  • I decided to use separate tables, because just in case that the row needs image, I will query the another table. Though you told in RDBMS the big values are stored in "off page", in another table I can delete image without scare of to break integrity. – andreshg112 Jun 12 '15 at 18:23
1

Whether a separate table for images is helpful depends on the cardinality of the relationship between the images and the rest of the attributes in the original table:

A separate image-table is justifiable if either:

  • multiple rows may contain the same image: enhances normalization since the image only has to be stored once in the image table instead of multiple times in the original table
  • an image can exist without the rest of the attributes; e.g. if the image for a row is exchanged/deleted, this could allow you to keep a history

If the images are unique per row, I would not worry about null fields, as you would have null foreign keys in those cases with a separate image table.

If you decide to keep the images in the original table, it may be beneficial to only retrieve them if needed (as is the case with any data, so preferably do not use SELECT * unless you need all the data available).

  • @David Spillett wrote "whatever best fits your conceptual model of your data": This is what I am referring to exclusively in my answer. If there are measurable performance concerns, he makes some excellent points to consider. – Marco Borchert Jun 12 '15 at 10:28
-2

If you create a new table then you would probably need a "JOIN" to get the image data. Joins can decrease performance of RDBMS. But in your case as there would be only one join so it won't have that much effect on over-all query performance.

  • 3
    JOIN is one of most important operations done on database, and in this case it will probably join on primary key so unless that join is done ALWAYS (and so separating it has no actual reason) then there is not much to save. – jkavalik Jun 12 '15 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.