Relational databse, photos, votes and warnings

Question

Hi, I'm designing a relational database, but I'm not too much experienced, so I'd like to ask a suggestion about the relational tables with photos.

I thought to use a table to store photos, and one or more tables for user data and subject links, so the photos can be linked to different subjects, for example to houses, or trees, in this case I could have this structure

table_houses
- house_id
- house_name
- house_architect_name, house_..., etc.

table_trees
- tree_id
- tree_name
- tree_plant_type, tree_..., etc.

table_photos
- photo_id
- photo_filename
- photo_date
- photo_user_id

table_rel_houses
- rel_id
- rel_house_id
- rel_photo_id
- rel_user_id
- rel_vote_id
- rel_warn_id

table_rel_trees
- rel_id
- rel_tree_id
- rel_photo_id
- rel_user_id
- rel_vote_id
- rel_warn_id

table_warns, table_votes, etc.

in this case, the relational tables should have the same structure, because the work in the same way, but point to a different subject (house or tree type)

could the structure of the data be correct or should I scompose much more the relational table in a table_rel_votes and table_rel_warns?

I'll need classical pages with a bigger photo and the thumbnails to navigate others, I must consider that the structure could store many millions of photos rows

Answer 1

You may want to consider modelling your database as follows:

table_photos
- photo_id
- photo_filename
- photo_date
- photo_user_id
- vote_id
- warn_id
- type
- detail_id

table_houses
- id
- name
- style

table_trees
- id
- name
- species

In this case, you can define your photo subject in the type field, such as 1 = Tree, 2 = House, etc. You will still be able to have many photos for the same subject without data duplication, but you will avoid having to build the table_rel_xxx tables with the repeated column schema. I prefer to avoid that when possible.

In this case, you would be able to build queries such as:

SELECT 
    table_trees.name
FROM
    table_photos
INNER JOIN
    table_trees ON 
    (table_trees.id = table_photos.detail_id AND table_photos.type = 1);

Or else simply query all the photos without "late binding" with the specific type:

SELECT 
    photo_filename
FROM
    table_photos;

You may be interested in checking out the following articles related to this database model:

• Stack Overflow - Polymorphism in SQL database tables?
• Data, Design, and SQL Server - Disjoint subtyping in SQL

These are techniques that attempt to implement polymorphic associations in a relational database, even though there is no support for this in SQL at a language level.

One drawback of this method is that it makes foreign key constraints quite tricky to define. You would need a foreign key constraint to have a guarantee that if table_photos is making a reference to a row in table_trees, that row really exists. A solution for the foreign keys problem is described, with a very good example, in the following EMC article:

• Distributed Keys and Disjoint Subtypes