I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to achieve this.

  • Place a column download_file as VARCHAR(250) and set to NULL by default (this because not all of the record will have a download related)

enter image description here

  • Create a relation between cm_identifier_type and a new table cm_download_file:

enter image description here

Now, regarding performance, disk space savings, query savings and so on, how would you do that? What is your recommendation on this edge case?

Note: for the moment I am using MariaDB 10.1.x but this will be in a MySQL instance, probably 5.x or so I am not sure at all since I didn't got those details yet

  • Is this a MySQL installation? Please clarify in your tags. – RLF Dec 31 '15 at 16:00
  • @RLF done, take a look to the NOTE on the OP, I can't add more tags – ReynierPM Dec 31 '15 at 16:13
  • 2
    You don't need to add erd unless you have a question about the diagram itself. This question is about database design, not the diagram per se. – Max Vernon Dec 31 '15 at 17:15
  • 1
    The answer to your question really depends on usage characteristics which you haven't supplied. If for instance, only 1 in 10 rows will have a download, then I'd tend to put that in a separate table. If 9 out of 10 will have a download then I might go the other way. What is the ratio of queries that need to display the download? – Max Vernon Dec 31 '15 at 17:16

If you have a lot of specific statistics about how often your optional columns will be null and how often the non-null columns need to be read (as opposed to your core, mandatory columns) then you could calculate space savings (or not) and at the very least you could derive some performance experiments to test each approach.

There is no rule of thumb for whether it is "better" to segregate optional columns into a separate table.

"Better" is a subjective term. What is of value? Disk space, CPU cycles, query response times, simplicity of code? You can't consider the relative merits of one approach over another without first considering what it is that you're trying to optimize.

There are a lot of reasons why you might want to move optional columns to a separate 1:1 related sub-table. See my answer to this question for more discussion of these reasons.

In your case, since you're concerned about space, you need to keep in mind a few things about how data is physically stored:

  • Variable size strings don't take up much space if they are empty.
  • Every nullable column needs a bit to indicate whether it is null or not.
  • A separate table with optional columns needs to store the primary key of the main table again (as a foreign key) and there may be a separate index on this column as well.

There are a lot of competing influences on the matter of which takes less space and which performs better. You need to consider:

  • How often is the optional data going to be missing? If it's really rare then maybe you will save space by segregating it out.
  • How often do you need to read the optional data? If you read it every time (whether it's there or not) then maybe joining for it all the time is less efficient.
  • On the other hand, if it isn't there very often and if you hardly ever read it, even when it is, then maybe it's more efficient to read shorter records containing only the mandatory columns?

Another thing to consider, perhaps even the most important thing to consider, is whether you are overthinking the issue by trying to pre-optimize. Disk is pretty cheap. CPU is pretty cheap. Programmers are expensive. Unless you need to be concerned about massive scale, maybe the best answer is the one that leaves you with the simplest (least buggy, easiest to maintain) code.

  • Great answer, I will go with first one, less JOIN when I need to get that column and BTW will not be queried all the time only when a record belongs to a category based on the previous relationship. thanks and happy new year – ReynierPM Dec 31 '15 at 23:32

I think the best option is the first solution, since the field is a VARCHAR: this means that no significant amount of space is occupied if the value is NULL.

On the other hand, with the second solution you have a table more to maintain, other indexes, need to join if you need the value, etc. Too much hassle for a problem that can be solved in a very simple way.

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.