Until now I thought I had a fairly firm grasp of Normalization, I am also open to the idea that my problem is ridiculously simple but am so involved with it now I can't see it! (as has happened many a time before).

Situation: -

I am trying to create some sort of to do 'actions' database, there are several different 'types' of action and each 'type' has different 'procedures' available. to summarise: -

Tables
Action Table: action_id, date_entered, date_due
Type Table: type_id, type_description
Procedure Table: procedure_id, procedure_description

ATP Table: action_id*, type_id*, procedure_id*

The Problem: -
Whilst a procedure is dependant on an action_id AND a type_id, the procedures available are narrowed down from purely the type_id. E.g. for an action (e.g. action no1), the user decides on a type (e.g. send email), the procedures available should then be limited to email procedures.
The problem with the tables above is that once an action is chosen ALL the procedures for all types are shown.

Anyone still following this gibberish? If so is this a database design issue or should this be resolved later with the front-end and queries?

Head hurts! Thanks

I am struggling a little, but it sounds like you are saying there is (or should be) another table TP that lists the Procedures available for a given Type, and that that TP table should be used to restrict permissible values of procedure_id in ATP?

In which case, all you need is a foreign key from ATP(type_id,procedure_id) to TP and your rule is enforced, right?

As far as lists of values are concerned, these are a front-end issue and should be based on an appropriate query. It is the constraints in the database that will enforce the rules, whether the LOV is right or wrong.

Your answer depends on the cardinality of the relationships
btn the tables. In other words, if there is (at most) only
a 1-to-1-to-1 relationship between all three tables then
you can simplify things considerably.

You should consider dropping the ATP table. You only need intersection tables (such as ATP)
when there is a one-to-many or many-to-many relationship
between tables. It's not clear from your explanation what
the cardinality btn your tables is.

I'm guessing that each action requires (at most) one procedure.
If that's true, then your tables should look like this:
(asterisk denotes primary key field)

Type Table: type_id*, type_description
Procedure Table: procedure_id*, TYPE_ID, procedure_description
Action Table: action_id*, PROCEDURE_ID, date_entered, date_due

I added the TYPE_ID field to the Procedure table because
that's how you would filter procedures based on type.
I added the PROCEDURE_ID field to the Action table
because (I'm assuming) there is at most one procedure
assigned to an action. This PROCEDURE_ID field can be
NULL when the action record is first inserted, and later
updated to reflect the choice of procedure.

-lv

Its ok i've sussed it now thanks, I created a relationship table which allocated all of the action_type's to action_procedures