Self referencing table or not?

[Stooshie]

Hi there,

I am creating a DB for an application that has components called packages, folders and pages. They have slightly different properties but some are the same. Some types can be "contained within" other types.

I am wondering if it is better to have one table for each type (package, folder, page) or have one table with a type_id and a parent_id.

Which do you think will be quicker for a select statement?

e.g. (SELECT from packages UNION SELECT FROM pages UNIO....) in the first case OR (SELECT FROM components c1 LEFT JOIN components c2 ON c2.parent_id...) in the second instance.

Thanks.

[Fumigator]

I've worked a lot with tables that store a more generic "object" type thing, so the rows in the table are mostly defined by relationships with other rows in that same table... and it does have its advantages. But it can also be a royal pain in the rump debugging problems.

If you have no real compelling reason to organize the data this way, for clarity's sake I would create separate tables for each object type.

When it comes to speed of data access, your two examples don't make sense to me. One is a union and one is a join, but wouldn't your different object types interact with one another in the same ways (either unioned or joined) regardless of which design you chose? If I'm right then the real question should be: Is joining a table to itself faster or slower than joining two different tables? And my answer is... I dunno... set up tests of both scenarios and find out.

[Stooshie]

Thanks Fumigator.

The reason I did a join in the one case was I wanted all the items from each table with the same parent id. And in the second instance (if they were all in the same table) I neede to do a join. Maybe a join in the first instance would be better anyway!

I will set up a test and see.

Thanks again.