Using Access 2000 to create a database.

[Daren Smith]

Good Morning All,
I am using Access 2000 to create a Database that will keep housing
information for clients at my place of work. The problem is that I am having
difficulties creating the relationships because they will be many to many
tables as opposed to using the primary key type of model. If anybody has any
advice I will gladly explain in a bit more details.

 

[scubadiver]

Do explain, please! What do your tables look like at the moment?

 

[Klatuu]

In a relational database, there can be no many to many relationships. Each
table should still have it's unique primary key. The usual way to resolve
this problem is with a third table. You will hear it called Junction table
or an Intersection table.

A good example is scheduling classess for students. Each student may attend
zero to many classes. Each class will have 0 to many students. All you need
is a Class/Student table that carries the primary key of each combination:

tblClassStudent
CLASS_STUDENT_ID - pk
CLASS_ID - fk To tblClass pk
STUDENT_ID fk to tblStudent pk

Now you can determine who is in which class and what classes a student is
taking.

 

[Jamie Collins]

In a relational database, there can be no many to many relationships. Each
table should still have it's unique primary key. The usual way to resolve
this problem is with a third table. You will hear it called Junction table
or an Intersection table.

I call it a relationship table, based on the design principle (not my
invention) that a table models either an entity type or a relationship
between entities but not both (other table types --lookup tables,
scratch tables, auxiliary tables, etc -- not withstanding).

A good example is scheduling classess for students. Each student may attend
zero to many classes. Each class will have 0 to many students. All you need
is a Class/Student table that carries the primary key of each combination:

tblClassStudent
CLASS_STUDENT_ID - pk
CLASS_ID - fk To tblClass pk
STUDENT_ID fk to tblStudent pk

You'd additionally need a key over CLASS_ID and STUDENT_ID to prevent
duplicates. Note the CLASS_STUDENT_ID pk column is superfluous and is
presumably intended as a so-called 'surrogate'.

Jamie.

--