Circular Referance (1 Viewer)

[yessir]

I have (for this post) 2 tables.

department
====================
[B]pk  | d_id
--------------------------
    | d_fund
fk1 | d_status_code_id
    | d_department
fk2 | d_building_id[/B]
fk3 | d_contact_id
fk4 | d_note_id
[B]    | d_modified
    | d_user[/B]

contact
====================
[B]pk  | c_id
--------------------------
    | c_banner
    | c_last_name
    | c_first_name
    | c_middle_initial[/B]
    | c_job_title
[B]fk1 | c_department_id[/B]
    | c_department_text
[B]    | c_email
    | c_phone
    | c_modified
    | c_user[/B]

As you can see there is a circular reference between them as each need the other's pk to ref a contact and department respectfully.

This will cause a problem during data entry will it not?

And if so, would removing the "req" status on either on remove the issue?

thanks

 

[ScottGem]

Your structure is incorrect. If each Department as only one Contact, then you only need ContactID as your FK in the department table. There should be nothing about Department in the Contact table. On the reverse, if each Contact has only one department, but a department can have multiple contacts, then you need DepartmentID as your FK and nothing about contact in the Department table. You also would not have Department Name in contacts, that would be redundant.

However, if a contact can be associated with multiple depts and a dept can have multiple contacts, then you have a many to many relation. To manage that you need a join table:

DeptContacts
DeptContactID (PK Autonumber)
ContactID (FK)
DeptID (FK)

 

[yessir]

thanks,

Actually the dept and contacts table are used as refs. (including to each other)

A dept can have a contact and that contact can be from the table,

A contact can be have adept and that can be from the table

but it is not necessary to have a defined relationship i believe other than they can be that way.... thus removing req and crossing fingers

 

[ScottGem]

thanks,

Actually the dept and contacts table are used as refs. (including to each other)

A dept can have a contact and that contact can be from the table,

A contact can be have adept and that can be from the table

but it is not necessary to have a defined relationship i believe other than they can be that way.... thus removing req and crossing fingers

Are you saying a Dept can have a single contact and a contact can have a single dept, but not necessarily the same dept that they are assigned to as a contact? That doesn't make a lot of sense. But i would still use a join table to express that.

 

[yessir]

i know.. it's confuddled...

I am repairing/ upgrading an older appln and am in the init stages of understanding it. this is what they currently had.


A person may have a dept or not. a contact doesn't have to be an employee.

a department may have a contact and they may be an employee or not.

THerefore you have to allow for both inclusions.

I am thinking a text field with a supplementary lookup on each table to allow easy filling in of known entities else type it in.
This is anightmare for data integrity but may have to do.

 

[ScottGem]

Again, what you are describing is governed by a join table. I gather you have 2 tables one for contacts and one for employees. Instead you should have one People table with a flag to indicate they are a contact or an employee.

if you can't do that then you expand the join to:

DeptID
PersonID
PersontType (Contact, Employee)

 

[yessir]

So then,

If I am entering a dept and a contact doesn't exist I must force the user to enter the contact info before hand, and if a dept doesn't exist then I must force the user to first create a dept.

Still seem circular. But it is the morning..

 

[ScottGem]

No. Departments and contacts are INDEPENDENT. Its the Join table that matches them together. You can have departments with no people assigned and people not assigned to a department. It is only when you add a record to the join table, matching a PeopleID to a DeptID that you create a relation between them.

 

[yessir]

thanks for your patience

 

[yessir]

FYI, i went with:

department_contacts
========================
[B]PK, FK1 | dc_department_id REFERENCES department
PK< FK2 | dc_contact_id    REFERENCES contact[/B]
-------------------------------

========================

Allowing the use of M-M relations if so desired but are not layed out in current version.

Thanks again

 

[ScottGem]

FYI, i went with:

department_contacts
========================
[B]PK, FK1 | dc_department_id REFERENCES department
PK< FK2 | dc_contact_id    REFERENCES contact[/B]
-------------------------------

========================

Allowing the use of M-M relations if so desired but are not layed out in current version.

Thanks again

I'm confused. The ID department and ID fields are your FKs. The table should have its own PK (an autonumber). If you want to create a unique index on the combination of dept and contact ID you can do that.

 

[yessir]

Ok, changed to this:

department_contacts
========================
[B]PK     | dc_id [/B]
-------------------------------
FK1,I1 | dc_department_id REFERENCES department
FK2,I1 | dc_contact_id    REFERENCES contact

========================

Here was the example I was following:
A table can contain more than one foreign key constraint. This is used to implement many-to-many relationships between tables. Say you have tables about products and orders, but now you want to allow one order to contain possibly many products (which the structure above did not allow). You could use this table structure:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Note also that the primary key overlaps with the foreign keys in the last table.

 

[ScottGem]

What you quoted makes sense up to the last piece. Though I wouldn't consider that a join table since it contains other data. But I do NOT recommend composite indexes. Its much easier to manage relations using a single PK. You can create multi-field indexes to make sure of uniqueness.

Also, that is not a good example. Lets say you are selling clothes and one of your products is Men's Socks (ProductID: 100). Now lets say you have qualfiiers for the product of size and color. That might mean you would have multiple line items in your order ProductID 100, for different colors and sizes.