Database design decision for storing files

Question

Hi I am working on a personal application and am facing a database design decision that I do not know which course of action to take.Here is what I am trying to accomplish.

In my application I will store a lot of images , videos and some html files build with a WYSIWYG editor .I have already decided that I will not be storing in my database the file but instead I will only store the original file name and the storage file name(unique identifier).

To that end I decided that it would be a good idea to have 3 separate tables for each type of file.

This is where I started to hit some problems.

At the moment , after I analyzed what I was trying to build , I realized that so far I have 4 types of images : avatars , thumbnails , description , headers.And this list may grow until I am finished with the application.The same could be said for the videos and html files , I am not sure yet.

As of now I see 2 courses of action in designing my database.

The first one is that I have one Images table in which I will have ths properties:

OriginalFileName ,StorageFileName , IsAvatar , IsThumbnail , IsDescription , IsHeader

In this case every time I identify a new type of image in my app I will have to modify the database table , witch does not sound right to me.

The second one is that I create tables for each type of Image.

In this case I would have 4 tables that would have a Primary column , a OriginalFileName column and a StorageFileName column.

Again every type I identify a new type of Image a new table will have to be created.

This solution kind of sounds right but I could be entering the overengineering teritory by doing things like this.

So what would be the best solution for my current problem?

I am opened to hear alternative options if anyone has any.

Answer 1

What you're suggesting (both suggestions) is called denormalisation; in a relational database this can be a bad thing. If avatars, thumbnails, descriptions and headers have the same attributes, i.e. only a original file name and a storage filename they should all go in one table, certainly, but you only need a single column to identify which is which. You then have a second table to decode this.

For instance:

You have a table, with columns

Files
    OriginalFileName, StorageFileName, FileTypeID

The FileTypeID would then be a foreign key into a second table:

FileTypes
    ID, Description
    1   Avatar
    2   Thumbnail
    3   Description
    4   Header

If you then want to add another type of file you add another row to FileTypes and start populating the table Files.

If your files have different attributes then you might want to consider 4 separate tables but not until that point.