Which one is correct and optimized in sql?

Question

Imagine that I have three entities (EntityA, EntityB, EntityC) that can have some images. So there are two ways:

Make an image table for each entity. It means that EntityA has a image table named AImages and similarly for EntityB and EntityC. This method is more intelligent but has more tables.
Have an image table and another table name EntityType that goes between.

EntityType table has an EntityTypeId column and a name and has three records: 1,EntityA, 2,EntityB, 3,EntityC.

Then in image table:

If I save a record for EntityA the record in image table would be this: 1,1,name

First column is ImageId, second one is EntityTypeId and the third one is image's filename.

If I save a record for EntityB the record in image table would be this: 2,2,name

And if I save a record for EntityA the record in image table would be this: 3,3,name

In this method the number of tables would decrease but the queries would be longer.

Which one is optimised or any other way...

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

Have an image table and another table name EntityType that goes between.

This will require you to enforce the referential integrity manually, which is trickier than it looks.

So either:

for each 1:N relationship, have a separate "N" table for each "1" table (as you proposed),
or employ something like exclusive FK or inheritance if you want to avoid too much structural duplication.

edited May 23 '17 at 11:57

Community

1
1

answered Apr 27 '14 at 13:13

Branko Dimitrijevic

50,809
10
93
167

score 0 · Answer 2 · answered Apr 27 '14 at 04:52

0

The raght way is to store images in varbinary(max) columns in their respective tables, and not to separate them. SQL Server will take care of phisically storing them in an efficient, off-row, manner.

Depending on which version of SQL Server is used and the size of the LOBs, you might consider FILESTREAM or FILETABLE feature.

answered Apr 27 '14 at 04:52

dean

9,960
2
25
26

I am working on a web application. Is it optimize to save a file in database? So what about space managing or server resources managing or query sizes? And each record of entities can have any number of images. – Hamid Reza Apr 27 '14 at 05:04
1

If it's 1:m relationship, go the separate tables route (one image table for one regular). Consider the filestream anyway (there is a link in the answer to the docs on the feature). – dean Apr 27 '14 at 05:26

score 0 · Answer 3 · answered Apr 28 '14 at 06:40

It seems having an BaseEntity table, that contains shared properties of other entities, will help you.
EntityA & EntityB & EntityC will be childes of the BaseEntity, their primary key will be the primary key of EntityBase table.
A zero-or-one relation between them will do.
Having a discriminator column called entyty_type in BaseEntity will be recommanded.

Other collections like Images and adresses and else will be connected to this EntityBase table.
enter image description here

Which one is correct and optimized in sql?

3 Answers3