Re: Unique constraints across tables?

This looks like poor analysis. There is a dependency between the two attributes as you have described.

A couple of questions first.

1. Does the data refer to a telephone service? i.e.. the telephone number of the service and the date that the service was terminated?
   • or -
2. Does the data refer to an employee, the date the employee finished working, and the telephone number that the employee had while working?

Lets assume the second...

The attributes of employee are likely to be  EMPLOYEE [emp_id,emp_code,first_name,last_name,middle_initial, date_of_birth]

You may think that employment start date and end date are attributes here as well but what about the employee who has multiple periods of employment over a much longer time? Therefore an employee must be allowed to have multiple relations to EMPLOYMENT_PERIOD [period_id, start_date, end_date]. This is a standard 3NF resulting in the intersecting entity EMPLOYEE_EMPLOYMENT_PERIOD [period_id,emp_id].

Now, lets think about the telephone number. ...hmmm. Does a telephone belong to an employee? No. Is it assigned to an employee? Yes. Is it a permanent assignment? No, well perhaps for their period of employment if that's the company policy, but it's most like to be assigned to the employee while working at a particular location within the company. That's the hint... it has something to do with location and time. This usually resolves to an entity CONTACT [contact_id, work_telephone_number, home_telephone_number, fax_number, email_address, url] and this is related to EMPLOYEE via EMPLOYEE_CONTACT [contact_id, emp_id, start_date, end_date]

So far, nothing tricky, just some questions and alternatives. Almost EVERY system has PARTY (sub-type to EMPLOYEE), LOCATION (sub-type to CONTACT), and PERIOD entities although most analysts don't understand it that way. These can be generalised to fit any system with minimal changes. Careful analysis of optional foreign keys will generally point out errors in your analysis (sorry, I was taught a very inflexible analysis method and have seen the results work very efficiently with very simple code).

This may give you something to work on. It's not as pretty as it could be. I'd need another half hour or so to flesh it out.

If the case is the first I described then you should re-evaluate the real relation. Again, it looks like poor analysis to set the tables up this way.

...neil {actually: neil [dot] hulin [at] litech [dot] freeserve [dot] co [dot] uk}

swilson669_at_my-dejanews.com wrote in message <72cocd$5sc$1_at_nnrp1.dejanews.com>...
>I am trying to find the best way to make two fields in two different tables
>unique together. I don't think that this can be done in a constraint, and
am
>thinking that it should be done in a trigger. I thought that I would just
>confirm my thoughts before doing this.
>
>The structure looks like this:
>
>TABLE1
>ID PK
>TERMINATION_DATE DATE NULL
>
>TABLE2
>ID FK
>TELNO VARCHAR2 NULL
>
>Telno and Termination_date are to be unique together not separately.
>
>I would appreciate any help, with this. Thanks in advance.
>SWilson
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Nov 12 1998 - 04:50:57 CST