0

After much research, I've decided to store pdf/img/txt/doc/etc files on a web server instead of a database. However, the reference to that file will be stored in SQL Server.

So far I have...


| File_id (int) | FileName (varchar(260)) | Uploaded Date (Datetime2) | File Extension (varchar(4)) |
|---------------|-------------------------|---------------------------|-----------------------------|
| 1             | Billing Invoice         | 2019-10-20 etc            | pdf                         |
| 2             | Receipts                | 2019-10-20                | jpeg                        |
| 3             | Orders                  | 2019-10-21                | txt                         |

My question is, should I store the file extension as is or create an extra table for lookup and use foreign key constraints?

| File_id (int) | FileName (varchar(260)) | Uploaded Date (Datetime2) | File Extension (FK to extension_id) |
|---------------|-------------------------|---------------------------|-----------------------------|
| 1             | Billing Invoice         | 2019-10-20 etc            | 1                         |
| 2             | Receipts                | 2019-10-20                | 2                        |
| 3             | Orders                  | 2019-10-21                | 3                         |

| Extension_id (tinyint) | Extension (varchar(4)) |
|------------------------|------------------------|
| 1                      | pdf                    |
| 2                      | jpeg                   |
| 3                      | txt                    |

I'm a pretty junior software engineer and I'm worried that over-normalizing might cause performance/readability problems later on or just cluttering the database by adding unnecessary tables.

  • The old adage, normalize until it's correct, denormalize until it works would apply here. I wouldn't make the extension a child table for this. As you say, it would only add complexity. – Jonathan Fite Oct 29 '19 at 12:21
  • Awesome. Thank you. I was trying to recall BCNF and applying it to this case. – redcabbage Oct 29 '19 at 17:13

Your Answer

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

Browse other questions tagged or ask your own question.