Re: Forms 4.5 - Master Detail Problem

>I'm trying to create a from which has two master detail relationships
>but have hit a problem. The background to the form is as follows:
>
>This part of the database is used to track employees and their skills.
>The entity/relationship diagram looks like:
>
>Employee---<Skill_profile>---Skill
>
>

You do not have 2 master details... that would suggest that for each employee, there are many skills (true, master detail #1), and for each skill there are many descriptions (not true). What you have is 1 master detail, and a lookup. There are 2 ways of doing this, depending on what you want to do...

#1 Use a post-query trigger to look up the description for the retrieved code. Hide the code and only display the description. [Easiest way]

#2 Create a view that does the join, then use that view as the detail. [More efficient, but requires more work in updates/deletes]

As an aside, what I usually do on something like this is to actually join all three tables. This give me a row for each combination of employee-skill. Why? Its a real pain to query on the detail records. If you put them in single view, you can query on skill level, description, or anything.

create view employee_skills as select
first_name, surname, grade, e.employee_no, sp.skill_code, sp.skill_level, s.description from employee e,skill_profile sp, skill s where e.employee_id=sp.employee_id and
sp.skill_code=s.skill_code

You will need to write ON-UPDATE, ON-DELETE and ON-INSERT triggers to update the correct tables. Since you are actually only inserting/delete/update the SKILLS_PROFILE table, you should add a primary key (based on a sequence #) that identifies the row, and include it in the view. Then the update/deletes are easy - do it on the row identified by the id. For the insert, it is null. Get it first and save it to the block variable, then do the insert. Then subsequent updates work without re-querying.

Hope this isn't to complicated...

Dan Hekimian-Williams Received on Tue Sep 15 1998 - 06:51:59 CEST