many-to-many relationships
Lynn Bills
front end and a group of Access tables (15 - 20) as the backend. The tables
have no relationships established and contain a lot of duplicate data. The
VB code pulls data fields from the tables as needed. I would like to
normalize these tables, establish relationships and use queries to reduce
the amount and complexity of the VB code. It seems like doing this would
neccesitate creating several many-to-many relationships in the database. My
questions are: What are the drawbacks of many many-to-many relationships?
Are there alternatives to creating these? The books I've seen covering
relationships do not go into any depth on many-to-many relationships. Can
anyone give me some general help or suggest references for this information?
Thanks.
--
Lynn Bills
Pete B
established that relates the two many-side tables, allowing the relationship
to be configured as two one-to-many joins.
For example, you may have, say, a list (table) of Classes and a list (table)
of Students. The Classes table might have a ClassID field as the PK
(Primary Key) and an associated CourseTitle field. The Students table might
have a StudentID field as the PK and an associated StudentName field.
Each of the classes in the Classes table can have many students from the
Students table, and each of the students in the Students table can be
attending many Classes in the Classes table. So each of the ClassID keys
can be related to many of the StudentID keys, and each of the StudentID keys
can be related to many of the ClassID keys. Thus it is a many
(Classes)-to-many (Students) relationship.
You resolve this by designing an intermediate table, say the Student
Schedule table. This table might have records with a SchedID field as the
PK, an associated StudentID foreign key (the PK from the Students table),
and an associated ClassID foreign key (the PK from the Classes table). Now
you can establish the relationships as standard one-to-many: each StudentID
in the Students table is related one-to-many to the StudentID FKs in the
Student Schedule table, and each ClassID in the Classes table is related
one-to-many to the ClassID FK in the Student Schedule table.
With this Student Schedule table in between the relationships, it is now
easy to query the Student Schedule table for a list of, say, all the
students in a class, or all the classes scheduled for a student, and only
one-to-many relationships are required.
Hope this helps a bit. What you need to do is to massage the present data
tables you have into a form where you can set up all of the relationships as
one-to-many, using a schema like this. You will almost assuredly have to
design some such intermediate tables. Their use is not mandatory (you can
do the same with just many-to-many joins), but it sure makes things simpler
to work with.
For a really good tutorial on this, try the book "Access Database Design and
Programming" by Roman/O'Reilly & Assoc. ISBN #1-56592-297-2. Short,
simple, and the best of the bunch.
--
Pete B
Lynn Bills wrote in message ...
Larry Linson
> I have inherited a decision support system consisting of a Visual
> basic front end and a group of Access tables (15 - 20) as the
> backend. The tables have no relationships established and contain
> a lot of duplicate data. The VB code pulls data fields from the
> tables as needed. I would like to normalize these tables, establish
> relationships and use queries to reduce the amount and complexity
> of the VB code. It seems like doing this would neccesitate creating
> several many-to-many relationships in the database. My questions
> are: What are the drawbacks of many many-to-many relationships?
> Are there alternatives to creating these? The books I've seen
> covering relationships do not go into any depth on many-to-many
> relationships. Can anyone give me some general help or suggest
> references for this information?
If there is logically a many-to-many relationship, there IS logically a
many-to-many relationship, no matter how one might use some alternative
description. The advantage of many-to-many relationships is that they
let you use your information as it exists, not by some "workaround".
The disadvantage is that neither Access nor Jet handles them
automatically, so you have to include your own processing. You define
them with a "junction" or "intersection" table with keys from both
tables. They are not included, and there is no way to include, the many-
to-many relationship in Access' Relationships collection, but the two
one-to-many relationships that result in the many-to-many can be
included.
In fact, I only establish Relationships in Access' Relationships
Collection when I want to enforce Referential Integrity. Often, I may
not want to automatically delete, for example, because it's important
to save the record in a history file, and you have to do that yourself.
Michael Kaplan, whose technical opinions I highly respect, tells me
that establishing the relationship will, in fact, improve performance,
but I haven't suffered too much from slow performance and they are a
Rouyal Pain in the Tender Places when I want to delete a table, or make
some design modifications while developing. But, if yours is a Decision
Support System, rather than a strategic business system, you may not
need a history file.
In a previous incarnation as a mainframer, I worked with DSS and it was
concerned with slicing-and-dicing-and-presenting existing data to ease
decision-making, rather than entering and saving data. (Of course, our
clients understood the capabilities of our product better than our
corporate marketing, and were using it for application development,
too, so thus we did on occasion have to address entering and saving for
history. <G>) I guess I can say "been there, done that, got the T-
shirt" because I _do_ have a T-shirt our department gave us to wear to
the annual outing, light green with white "DSS" letters ironed on. <G>
(When I wear it now, everyone assumes my initials are D.S.S.. :-( )
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.