Setting a Key to update cascading table

[dBNovice]

Hello All, I have a DB that have 6 tables - tblCreator, tblSummary,
tblPaper, tblProposition, tblContributor, tblPanel. The tables are
linked in the following way:
tblSummary is linked with tblProposition by ProposalID
tblCreator is linked with tblProposition by ProposalID
tblPanel is linked with tblPaper by PanelID
tblPaper is linked with tblContributor by PaperID

Their are two ways that a proposition is made:
1. A Creator proposes a Panel with several Papers and their
Contributors and Summaries.
2. An individual paper is proposed with a Summary and the Contributors.
A unique identifier (CreatorID) is assigned to each of the
propositions. Each paper is given a PaperID. Individual papers are
then combined to be in a panel and then each panel is given a PanelID.

Some contributors are on multiple panels. I want to assign a unique
identifer (ContributorID) to each contributor (author, co-author,
creator, etc...). I want to be able to set up the system such that
when I change any personal info on any given contributor it would
change in all tables where the contributor is mentioned. Please if you
understand what I'm trying to do, can you provide me with some help on
how to proceed. My VBA is limited but I'm learning fast.

[David W. Fenton]

"dBNovice" <lu*****@hotmail.com> wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:

Hello All, I have a DB that have 6 tables - tblCreator,
tblSummary, tblPaper, tblProposition, tblContributor, tblPanel.
The tables are linked in the following way:
tblSummary is linked with tblProposition by ProposalID
tblCreator is linked with tblProposition by ProposalID
tblPanel is linked with tblPaper by PanelID
tblPaper is linked with tblContributor by PaperID

Their are two ways that a proposition is made:
1. A Creator proposes a Panel with several Papers and their
Contributors and Summaries.
2. An individual paper is proposed with a Summary and the
Contributors. A unique identifier (CreatorID) is assigned to each
of the propositions. Each paper is given a PaperID. Individual
papers are then combined to be in a panel and then each panel is
given a PanelID.

Some contributors are on multiple panels. I want to assign a
unique identifer (ContributorID) to each contributor (author,
co-author, creator, etc...). I want to be able to set up the
system such that when I change any personal info on any given
contributor it would change in all tables where the contributor is
mentioned. Please if you understand what I'm trying to do, can
you provide me with some help on how to proceed. My VBA is
limited but I'm learning fast.

Can a paper have more than one author (i.e., Contributor)? If not,
then you already have what you want. Actually, just re-reading your
description, you do need to make a change. If there's only one
contributor per paper, then store ContributorID in tblPapter
(instead of storing PaperID in tblContributor). tblContributor will
be linked to as many papers as that contributor has written, and
that in turn is linked through tblPaper to the panel, which would
then get you the list of the people on the panel.

If papers can have multiple authors, then you need to remove
ContributorID from tblPaper and use a many-to-many join table
between tblPaper and tblContributor. That table would have two
columns, ContributorID and PaperID (assuming those are the primarky
keys of both tables), and you'd make the combination of those two
fields a compound primary key.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[dBNovice]

Thank you for your attempt to help. Let me see if I can clarify the
structure of the tables. tblPaper has one entry for each paper but
each paper can have multiple contributors. tblPanel has multiple
papers, and multiple contributors, tblCreator has one contributor. All
the contributors are listed in tblContributor.

[David W. Fenton]

"dBNovice" <lu*****@hotmail.com> wrote in
news:11**********************@e56g2000cwe.googlegr oups.com:

Thank you for your attempt to help. Let me see if I can clarify
the structure of the tables. tblPaper has one entry for each
paper but each paper can have multiple contributors. tblPanel has
multiple papers, and multiple contributors, tblCreator has one
contributor. All the contributors are listed in tblContributor.

That clarifies nothing at all, because you've haven't given the
relationships.

I would think that tblPanel would be the parent of tblPaper and
tblPaper the parent of a table that links to tblContributor. I'm not
sure what tblCreator is doing in there, unless tbleContributor is
the join table between tblPaper and tblCreator.

Put in plain language:

A panel can have multiple papers.

Each paper can have one or more contributors.

Thus, tblPanel is in a 1 to many relationship with tblPaper, with
PanelID stored as foreign key in tblPaper.

The question is how to handle the relationship between a paper and
the conributor(s). If you're allowing for more than one author, you
need a join table. I'd call it tblPaperCreator and it would have
PaperID and CreatorID (assuming that that's the table where you
store the names and so forth of the people who write the papers).
There's a 1 to many relationship on PaperID between tblPaper and
tblPaperCreator. Likewise, there's a 1 to many relationship on
CreatorID between tblCreator and tblPaperCreator.

That structure handles all the parts and requires no duplication of
information. In that structure, there is no purpose for
tblContributor, unless it is the name you give to your many-to-many
join table (what I called tblPaperCreator). I always name my join
tables so as to indicate exactly which entities are being joined. I
think having both tblContributor and tblCreator is confusing
terminology, and would use one or the other.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[dBNovice]

I'm sorry the names of the table are confusing. tblCreator is the
creator of the proposition -- either panel or paper (i will chg name to
tblProposalCreator). Although papers can be submitted individually;
ultimately they will be added onto a panel. But initially I have
panels and papers that all have contributors and those contributors
could be authors, creators, presenters, chairs, discussants. Because
the contributors are in different tables, I want to set up the DB such
that when I make a change to a contributor contact info in
tblContributor their corresponding info is updated in tblCreator,
tblPanel, tblPaper, etc...

[David W. Fenton]

"dBNovice" <lu*****@hotmail.com> wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:

I'm sorry the names of the table are confusing. tblCreator is the
creator of the proposition -- either panel or paper (i will chg
name to tblProposalCreator). Although papers can be submitted
individually; ultimately they will be added onto a panel. But
initially I have panels and papers that all have contributors and
those contributors could be authors, creators, presenters, chairs,
discussants. Because the contributors are in different tables, I
want to set up the DB such that when I make a change to a
contributor contact info in tblContributor their corresponding
info is updated in tblCreator, tblPanel, tblPaper, etc...

There shouldn't be any information about the contributor except in
tblContributor. The only thing about contributors that you should be
storing in another table is the ContributorID.

That's the way relational databases work. You enter the information
about one thing in one record in one table and then link to that
record from all the other records that relate to it.

I would get rid of separate tables for authors, creators,
presenters, chairs and discussants -- they are all the same entity,
just differentiated by role, and should all be in a single table
(e.g., tblPerson). Then you can link them to the panel with a
many-to-many join table that has the PanelID, the PersonID and a
field that stores the role on the panel, i.e., author, creator,
presenter, chair or discussant.

You can tell that your multi-table model is flawed because you have
several tables with nearly identical structure, and because you may
need to enter data about the same person in more than one of the
tables.

I almost never have more than one table in any app that stores
information about people.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[dBNovice]

Thank you for your input. I didn't design the tables initially but I
am trying to streamline them to make them more efficient. I will work
on that this weekend and take your comments into consideration.