If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

similar Tables or a more generic table

I'm creating a project of my own and I have a set of tables that represent entities that may share another common table.

For example:

Let's say I have (4 tables, but I show only GOAL and PROJECT):

GOAL
---------
Goal_ID (PK)
....

PROJECT
--------
Project_ID (PK)
...

I'd like to have a set of Requirements for Goals and Projects.

So I create:
REQUIREMENT
-----------------
Req_Id (PK)
Related_Class_ID (instance would be a Goal_ID or a Project_ID
Related_Class (instance would be 'PROJECT' or 'GOAL')
Description
....

With this I can have a table that can save requirements for Goals and
for Projects (or Activities).

My question is as follows: What if there are other characteristics that are
shared by a Project or a Goal like let's say: Benefits.

Should I create another table like REQUIREMENT, like in:

BENEFITS
-----------------
Benefit_Id (PK)
Related_Class_ID (instance would be a Goal_ID or a Project_ID
Related_Class (instance would be 'PROJECT' or 'GOAL')
Description

or instead I should create a more generic table that can include REQUIREMENTS and BENEFITS like in:

CLASS_DETAIL
-------------------
Detail_ID
Related_Class_ID (instance of Goal_ID or Project_ID)
Related_Class (instance would be 'PROJECT' or 'GOAL'
Category_Type (instance would be 'REQ' or 'BEN' from CATEGORY table)
Description

I can do both but could your give me please some hints as to what implementation would be better?

Re: similar Tables or a more generic table

It's difficult to give a definite answer without knowing your requirements in more detail. Do REQUIREMENTS and BENEFITS really have the same structure, i.e. just a Description, or do they actually each have a set of attributes of their own? If they have the same simple structure, then combining them as you have suggested may be OK; if not, then it would be better to keep them separate.

Your design for the Related_Class_ID and Related_Class columns means that you cannot define foreign key constraints to ensure referential integrity - you would have to use triggers. Some alternatives you could consider:

This requires a check constraint e.g. CHECK( (goal_id IS NOT NULL and project_id IS NULL) OR (goal_id IS NULL and project_id IS NOT NULL) )

It actually simplifies your SQL, because you can join master to detail with one condition (req.goal_id = goal.goal_id) rather than two (req.related_class_id = goal.goal_id AND req.related_class = 'GOAL').

2. If PROJECT and GOAL are quite similar entities, you may prefer to design them as subtypes of a more generic entity. I'll call it THING, but you should be able to come up with a more meaningful name (if you cannot, then perhaps this approach is not right):

With this approach, it is a good idea to create views that pre-join the subtypes to their supertype, for the convenience of users and applications, e.g.
CREATE VIEW v_project AS
SELECT thing.thing_id, thing.[other_columns]
[project.columns]
FROM thing, project
WHERE project.thing_id = thing.thing_id;

These view will look exactly like the original tables before you introduced the supertype. Some database (e.g. Oracle) allow inserts, updates and deletes on such views e.g. via triggers.

Re: similar Tables or a more generic table

Thanks a lot Andrew,

I was thinking that REQUIREMENTS and BENEFITS should
definitely have different attributes in order to require
different tables. So thanks for your suggestions and I think
that's the way to go. I appreciate your valuable insights
as to how to design what I'm doing.

Regarding the THING Table For GOAL and PROJECT, I was thinking if
this should still be applied if there is a 0-M relationship
between the GOAL and PROJECT tables themselves. I think I still can
altought in fact, I have a total of 4 tables structured to represent a Tree
as follows:

1 MISSION can have 0-M GOALS; 1 GOAL can have 0-M PROJECTS;
1 PROJECT can have 0-M ACTIVITIES;

For this scenario I was thinking on creating a table or view with
a cross-reference for all ID's in case I want to get faster to all
ACTIVITIES involved on a certain MISSION.

Re: similar Tables or a more generic table

Igor,

I think the supertype could still be used here - I still call it THING but I'm sure a better name could be found!

I have sketched a simple ERD to illustrate - hope it loads up OK.

As for the MISSION_ACTIVITIES table or view: I would definitely prefer to go for the view - it will look after itself, whereas a table would have to be maintained and kept in sync with the base tables (e.g. via triggers). The view is quite simple, so I would not expect performance to be a problem.