I am creating a relational database, but I am little experienced, so Let me request an indicator concerning the relational tables with photos.

I figured to utilize a table to keep photos, and a number of tables for user data and subject links, therefore the photos could be associated with different subjects, for instance to houses, or trees, within this situation I possibly 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.

Within this situation, the relational tables should have a similar structure, since the work in the same manner, but point to another subject (house or tree type).

Is the structure from the data be correct or must i scompose a lot more the relational table inside a table_rel_votes and table_rel_warns?

I'll need classical pages having a bigger photo and also the pictures to navigate others, I have to take into account that the dwelling could store millions of photos rows.

You might want to consider modelling your database the following:

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

Within this situation, you are able to define your photo subject within the type area, for example 1 = Tree, 2 = House, etc. You still have the ability to have numerous photos for the similar subject without data duplication, however, you will avoid needing to build the table_rel_xxx tables using the repeated column schema. I favor to prevent that after possible.

Within this situation, you'd have the ability to build queries for example:

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

Otherwise simply query all of the photos without "late binding" using the specific type:

SELECT 
    photo_filename
FROM
    table_photos;

You might be thinking about looking at the next articles associated with this database model:

They are techniques that make an effort to implement polymorphic associations inside a relational database, despite the fact that there's no support with this in SQL in a language level.

One disadvantage to this process is it makes foreign key constraints quite tricky to define. You'd require a foreign key constraint to possess a guarantee when table_photos is creating a mention of the a row in table_trees, that row really is available. An answer for that foreign secrets issue is referred to, with an excellent example, within the following EMC article: