mysql - store typically/mostly NULL columns in one table or create 1:1 relationship?

Question

Running Mysql Server version: 5.7.27-0ubuntu0.18.04.1

I'm creating a site/app where a user "submission" can be one of:

Text Comments
picture/file upload
video/file upload (more or less technically same as #2, just with different mime type)

I'm having trouble deciding between the two designs (shortened for brevity)...

CREATE TABLE submissions
(
    submissionID            INT,
    userID                  INT,
    submissionComments      TEXT,
    fileDirectory           VARCHAR2(32), -- starting here these are only used 20% of time
    fileName                VARCHAR2(128)
    fileMimeType            VARCHAR2(128),
    fileSize                INT,
    originalFileName        VARCHAR2(64)
)

-OR-

CREATE TABLE submissions
(
    submissionID            INT,
    userID                  INT,
    submissionComments      TEXT
)

CREATE TABLE submissionFiles
(
    submissionFileID        INT,
    submissionID            INT, -- FK to submissions table
    fileDirectory           VARCHAR2(32),
    fileName                VARCHAR2(128),
    fileMimeType            VARCHAR2(128),
    fileSize                INT,
    originalFileName        VARCHAR2(64)    
)

I'm assuming text comments will prob be 70-80% of submissions.

So, the question becomes, is it better to use a single table and have a bunch of NULL values in fileDirectory/fileName/fileMimeType/fileSize/originalFileName? Or, is it better to have a 1:1 relationship to support when files are uploaded. In that case, I'd be creating both a submissions and submissionFiles record. Obviously most queries would then require joining the two tables.

This essentially comes down to not having a good understanding of the impacts of VARCHAR (and 1 INT) columns in tables where they are majority NULL. I'm probably pre-optimizing a bit here considering this is a brand new site/app, but i'm trying to plan ahead.

Late addition 2nd question (as I type this out), i see that TEXT is capable of handling: 65,535 characters or 64 KB. That seems like a lot for what a typical user would be submitting (probably less than 500 characters). It would eat up storage pretty quick. Would would be the impacts of making submissionComments into VARCHAR(500) instead of TEXT? I'm assuming if anything, there are no negative trade-offs besides being able to store "less".

Thanks!

Edit: as madhur pointed out, there are similar questions/good answers about "design patterns". i'm more concerned about performance. does the presence of large number of varchar's negatively impact data storage/retrieval (by messing up the way mysql implements pages/extents/etc)?

Will picture/file upload also have comments ? If yes, then check these duplicates: https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database , https://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database , https://stackoverflow.com/questions/57905620/db-design-question-about-nullable-foreign-keys-and-normalization/57931330 — Madhur Bhaiya, Sep 18 '19 at 12:31
thanks for the references, i agree they are very similar. however, they seem to focus on design patterns, and not necessarily performance. i guess i'm more worried about performance. in other words, does having a bunch of NULL columns in mysql mess up performance because of the impacts to pages/extents (the way the data is stored and retrieved). — user3249281, Sep 18 '19 at 12:41
You dont need to worry much about `NULL` values, if your tables are InnoDB (which they should be). Personally, I would prefer to avoid `NULL` columns. So, second usecase is closer to that, and a good tradeoff b/w overnormalization and denormalization. — Madhur Bhaiya, Sep 18 '19 at 12:44
The single table is the way to go unless a submission could contain multiple files.. The single table will be easier to implement+maintain and will also perform better than having two tables. The space requirement for NULL columns is negligible so there is really no benefit with two tables (submission is one entity in real life). What you might want to consider is to add a type-column to the table so your queries / application knows directly if it should process a text or a file. — slaakso, Sep 18 '19 at 13:19

score 0 · Answer 1 · answered Sep 18 '19 at 20:34

I have built schemas either way. At some level, it does not matter. But you may find that certain queries are faster one way (or the other way). The disk usage is about the same.

Your second option allows for (and hence implies) multiple 'files' per 'submission'. For such a "many:1" relationship, you must use 2 tables.

On the other hand, if there can there be only one "file" per "submission", you don't need submissionFileID (which I assume was intended to be the PRIMARY KEY??) Instead, use PRIMARY KEY(submissionID) for that second table.

If you wish to discus further, please provide the full CREATE TABLE, including NULL or NOT NULL, the PRIMARY KEY of each table, and any secondary indexes.

submissionComments into VARCHAR(500) instead of TEXT?

No storage difference.
No speed difference.
The former would truncate, giving a warning or error, at 500 characters; the latter would truncate at 65535 bytes. I would simply use TEXT.

Back to the main question. Your example has several columns that are either all NULL or all filled in. Hence, I would lean toward 2 tables.

mysql - store typically/mostly NULL columns in one table or create 1:1 relationship?

1 Answers1