junction tables

[lefty]

Per access help, i have created a junction table between ContactsTbl
and CompanyTabe to create a many to many relationship between the two
tables. The junction table contains ContactID and CompanyID fields
from each each of the tables. I've created a relationship between
ContactID in ContactTbl and ContactID in Junction table as well as a
relationship between CompanyID in CompanyTbl and CompanyID in junction
table. I'm having trouble wrapping my brain around the needed
relationships...and whether or not they should have referential
integrity. i would think that when a company is deleted, i would want
any corresponding contacts in the company deleted but if i deleted
contacts, obviously wouldn't want the company deleted. Any suggestions
on what types of joins? are the relationships i created correct?

Thanks
Lefty

 

[Arvin Meyer [MVP]]

Enforce referential integrity on both relationships. Generally it's not a
good idea to cascade deletes anywhere, but in this case, if a contract is
deleted, you want the detail record that represents the compound key, to
also delete, so turn on Cascade deletes from Contracts to the junction
table.

 

[lefty]

Enforce referential integrity on both relationships. Generally it's not a
good idea to cascade deletes anywhere, but in this case, if a contract is
deleted, you want the detail record that represents the compound key, to
also delete, so turn on Cascade deletes from Contracts to the junction
table.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com







- Show quoted text -

Thank you,
I'm assuming that by contracts you mean contacts? Just so i can be
sure.

 

[Arvin Meyer [MVP]]

Yes, Contacts and ContactID

 

[Ken Sheridan]

Is the relationship type between Companies and Contacts really many-to-many?
That would mean that each contact can belong to more than one company, which
is perfectly feasible of course, but unusual. Mostly a contact can belong to
one company only, so the relationship type is one-to-many, merely requiring a
CompanyID foreign key in the Contacts table.

Incidentally you can model a one-to-many relationship type with a table,
just as you'd model a many-to-many relationship type. The difference is that
the column in the 'junction' table which references the table on the one-side
(the referenced table), CompanyID in this case, is indexed uniquely. The
reason for doing this is that it avoids any Null foreign keys in the table on
the many-side (the referencing table) if it does not reference a row in the
referenced table (a contact who is such as an individual, not belonging to a
particular company in this case). A Null, being the absence of a value
rather than a value as such is semantically ambiguous. Opinions vary on
their use, some people are prepared to allow them, others not.

Ken Sheridan
Stafford, England

 

[lefty]

Yes, Contacts and ContactID
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com







- Show quoted text -

Thanks for the help. I set those relationships up but am haveing a
problem with creating subdatasheet in the CompanyTbl.
i selected the junction table in the subdatasheet wizard but don't
know what to do with the child and master fields. it seems every
combination i try reterns no records in the subdatasheet. any
thoughts?
thanks

 

[lefty]

Is the relationship type between Companies and Contacts really many-to-many?
That would mean that each contact can belong to more than one company, which
is perfectly feasible of course, but unusual. Mostly a contact can belong to
one company only, so the relationship type is one-to-many, merely requiring a
CompanyID foreign key in the Contacts table.

Incidentally you can model a one-to-many relationship type with a table,
just as you'd model a many-to-many relationship type. The difference is that
the column in the 'junction' table which references the table on the one-side
(the referenced table), CompanyID in this case, is indexed uniquely. The
reason for doing this is that it avoids any Null foreign keys in the table on
the many-side (the referencing table) if it does not reference a row in the
referenced table (a contact who is such as an individual, not belonging to a
particular company in this case). A Null, being the absence of a value
rather than a value as such is semantically ambiguous. Opinions vary on
their use, some people are prepared to allow them, others not.

Ken Sheridan
Stafford, England






- Show quoted text -

Thanks for the response. I would tend to agree with you regarding the
first point but i have a couple of clients that will occaisionally
switch companies and even sometimes will work for more that one
company at the same time. clients that work for more than one company
at the same time are rare but do occur. i'm pretty green with the
application but thought this type of relationship would be the way to
go in this case.

 

[Arvin Meyer [MVP]]

Thanks for the help. I set those relationships up but am haveing a
problem with creating subdatasheet in the CompanyTbl.
i selected the junction table in the subdatasheet wizard but don't
know what to do with the child and master fields. it seems every
combination i try reterns no records in the subdatasheet. any
thoughts?

Subdatasheets are only of any good in tables, and you should not ever be
editing data in tables. Subdatasheets will slow down the connection of data
immeasureably and should really never be used at all. Use a form and subform
for your data entry/editing.

With the Master/Child connection in a Form/Subform, you use the same field
that you used when you set up the PrimaryKey/ForeignKey relationship. As an
example, look at the Northwind sample database that came with Access. Look
at the relationships between the Orders and OrderDetails tables and the
relationship between the Products and OrderDetails tables. OrderDetails in
the junction table. Now look at the Master/Child link in the form/subform
for orders. That should help you understand.

 

[lefty]

Thanks for the help. I set those relationships up but am haveing a
problem with creating subdatasheet in the CompanyTbl.
i selected the junction table in the subdatasheet wizard but don't
know what to do with the child and master fields. it seems every
combination i try reterns no records in the subdatasheet. any
thoughts?
thanks- Hide quoted text -

- Show quoted text -

After looking a little closer, i realize that there is not data in my
junction table. i don't think i'm using the junction table and
relationships correctly. i thought that the relationship would cause
the junction table to populate with new records that correspond with
records in the contact and company tables. in other words, with both
table linked to the junction table, i thought it would create new
records in the junction table for each company/contact combination. am
i missing the boat on this one?

thanks

 

[lefty]

Subdatasheets are only of any good in tables, and you should not ever be
editing data in tables. Subdatasheets will slow down the connection of data
immeasureably and should really never be used at all. Use a form and subform
for your data entry/editing.

With the Master/Child connection in a Form/Subform, you use the same field
that you used when you set up the PrimaryKey/ForeignKey relationship. As an
example, look at the Northwind sample database that came with Access. Look
at the relationships between the Orders and OrderDetails tables and the
relationship between the Products and OrderDetails tables. OrderDetails in
the junction table. Now look at the Master/Child link in the form/subform
for orders. That should help you understand.

Thanks for the tip. i'd rather use forms anyway for data entry because
there will be less informed users of this database and i'd rather keep
them away from tables. I will look closly at Northwind.

 

[Ken Sheridan]

In that case the relationship type is many-to-many, so your model is spot on.

Ken Sheridan
Stafford, England

 

[Arvin Meyer [MVP]]

After looking a little closer, i realize that there is not data in my
junction table. i don't think i'm using the junction table and
relationships correctly. i thought that the relationship would cause
the junction table to populate with new records that correspond with
records in the contact and company tables. in other words, with both
table linked to the junction table, i thought it would create new
records in the junction table for each company/contact combination. am
i missing the boat on this one?

That boat was never built <g> You could build an Append query and force all
the combinations, but that is like creating lots of dummy records. The thing
that makes a relational database so efficient is that In a one-to-many or
many-to-many relationship there does not have to be data. So in the
OrderDetails table, every order does not have to contain every product.

To add data to the junction table, go to the company record that you want,
use a hidden textbox for the CompanyID in the subform and a combobox for the
ContactID (so you can show the contact's name instead of ID). As you select
contact names, you populate rows which creates the data you are looking for.
The Compound Primary Key makes it impossible for you to select the same
contact twice for that company.