Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003

    Normalization problem

    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: -

    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

  2. #2
    Join Date
    Sep 2002

    Re: Normalization problem

    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.

  3. #3
    Join Date
    Nov 2003

    Re: Normalization problem

    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.


  4. #4
    Join Date
    Oct 2003
    Its ok i've sussed it now thanks, I created a relationship table which allocated all of the action_type's to action_procedures

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts