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
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
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:
- Stack Overflow - Polymorphism in SQL database tables?
- Data, Design, and SQL Server - Disjoint subtyping in SQL
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: