CODEWITHSUNDEEP
sqldatabase-design
Oliver
Oliver

Reputation: 11597

Mapping a table to lots of other tables?

I have a website I develop that is structured a little like stackoverflow. I have a Posts table and a PostImages table in the database. The PostImages table looks like this:

PostImageId PK
PostId      FK
Uri
MimeType

So posts can have lots of images associated with them.

I now have the requirement that other places on the site should have images: Users should be able to have images on their profile, Categories should be able to have images, and Addresses should be able to have images.

It seems best to only have a single Images table in my database. How should I model this? I could have a single table like this:

ImageId    PK
PostId     FK
CategoryId FK
AddressId  FK
UserId     FK
Uri
MimeType

I could have a single images table, then extra tables for each kind of object:

PostImageId      PK FK
PostId           FK

CategoryImageId  PK FK
CategoryId       FK

(where the PK of each item is also FK to an image from the Images table)

There are some other strategies, too: for instance, I could have a ___Images table for each type, and no shared Images table. I could have a many-to-many mapping table for each, and perhaps there are some other solutions I haven't thought of.

What is best? I don't know that much about database design - which will be the most flexible and easiest to use?

Upvotes: 2

Views: 206

Answers (2)

Oliver
Oliver

Reputation: 11597

I went with the second choice. I have an Images table:

Images
    ImageId    PK
    Name
    Uri
    Width
    Height
    MimeType

and several mapping tables:

PostImages
    PostImageId      PK FK
    PostId           FK

CategoryImages
    CategoryImageId  PK FK
    CategoryId       FK

I prefer this solution as I can add more mapping tables without changing the Images table. It's easier to maintain. Also, it allows the Images table to be more semantic as it only contains data about images.

The cost of the join isn't a big problem as I only return a small number of images in any query. However, I haven't tested the relative speed of the two methods.

Upvotes: 1

polin
polin

Reputation: 2835

In real life projects, Database is designed regarding to the cost of accessibility of data. You mentioned two ways of designing your tables. Both are correct. If you put all your fields in one table there will be data redundancy. If you make two tables there is no problem.

But you have to keep in mind that joining two tables will cost more. So while fetching data from your server, this may make your page slow (If concurrently a lot of users try at the same time).

On the other hand, if you put it in one field, it will take more memory in database but less cost in data fetching. The choice is yours.

Upvotes: 1

Related Questions