Is it bad to implement supertype and subtype to the entire data in a database? I need some advice on this before moving to this direction...
For instance,
I have these tables as objects and they are related,
users
pages
images
entities
table as the supertype
entity_id entity_type
1 page
2 page
3 user
4 user
5 image
6 image
users
table
user_id entity_id
1 3
2 4
pages
table
page_id entity_id
1 1
2 2
images
table
image_id entity_id
1 5
2 6
here is the table to map images
table with entities
table because some images belong to certain page (maybe to blog posts, etc in the future),
map_entity_image
table
entity_id image_id
1 1
1 2
so, I will insert a row into the entities
table when I have a page, an image, an user, etc to be created.
in the end of the day the rows in this tables will increase in a great numbers. so my worry is that can it cop with large numbers of rows? will this database gets slow and slower in time?
after all, are these a bad structure?
or maybe I am doing supertype/ subtype incorrectly?
edit:
I think the entity
should have these data only,
entity_id entity_type
1 page
2 page
unless I want to attach images to users, etc. then it should be like this,
entity_id entity_type
1 page
2 page
3 user
4 user
maybe I am wrong...
EDIT:
so this is the query how I find out how many images attached to the page id 1,
SELECT E.*, P.*, X.*,C.*
FROM entities E
LEFT JOIN pages P ON (P.entity_id = E.entity_id)
LEFT JOIN map_entities_images X ON (X.entity_id = E.entity_id)
LEFT JOIN images C ON (C.image_id = X.image_id)
WHERE P.page_id = 1
returns 2 images.