Implications of Supertype and Subtype

Question

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.

Answer 1

If all you need is to attach images to users and pages, I'm not sure a full-blown category (aka. "subclass", "subtype", "inheritance") hierarchy would be optimal.

Assuming pages/users can have multiple images, and any given image can be attached to multiple pages/users, and assuming you don't want to attach images to images, your model should probably look like this:

---

You could use category hierarchy to achieve similar result...

...but with so few subclasses I'd recommend against it (due potential maintainability and performance issues). On the other hand, if there is a potential for adding new subclasses in the future, this might actually be the right solution (ENTITY_IMAGE will automatically "cover" all these new subclasses, so you don't need to introduce a new "link" table for each and every one of them).

BTW, there are 3 major ways to implement the category hierarchy, each with its own set of tradeoffs.

Answer 2

Not exactly an answer to your question, but, what you are describing is not what most modelers would refer to as a "supertype".

This is analogous to super/sub classes in OOP. The supertype is a genric entity, and, the subtype is a more specialized version of the generic entity

The classic example is vehicles. A "vehicle" has a common set of attributes like "owner" , "price", "make", "model". It doesn't matter whether its a car, a bicycle or a boat. However cars have "wheels", "doors" "engine-size" and "engine-type", bicycles have "number-of-gears" and "terrain-type" (BMX, road etc.) and boats have "propellers", "sails" and "cabins".

There are two ways of implementing this.

Firstly there is a "rollup", you have one table which holds all the common attributes for a "vehicle" plus optional attibutes for each type of vehicle.

Secondly there is a "rolldown", you have one table which holds only the common attributes for every vehicle. And one table for each vehicle type to hold the attibutes specific to "cars", "bicycles" and "boats".