Setting a Key to update cascading table

**David W. Fenton** · Mar 1 '06, 03:05 AM

Re: Setting a Key to update cascading table

"dBNovice" <luv4nrt@hotmai l.com> wrote in
news:1141159095 .412772.114810@ j33g2000cwa.goo glegroups.com:
[color=blue]
> 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.[/color]

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** · Mar 1 '06, 03:55 PM

Re: Setting a Key to update cascading table

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** · Mar 1 '06, 08:35 PM

Re: Setting a Key to update cascading table

"dBNovice" <luv4nrt@hotmai l.com> wrote in
news:1141227826 .335209.124380@ e56g2000cwe.goo glegroups.com:
[color=blue]
> 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.[/color]

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** · Mar 2 '06, 06:39 PM

Re: Setting a Key to update cascading table

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
tblProposalCrea tor). 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** · Mar 2 '06, 07:55 PM

Re: Setting a Key to update cascading table

"dBNovice" <luv4nrt@hotmai l.com> wrote in
news:1141324293 .319587.282810@ j33g2000cwa.goo glegroups.com:
[color=blue]
> 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 tblProposalCrea tor). 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...[/color]

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** · Mar 3 '06, 03:45 PM

Re: Setting a Key to update cascading table

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.

Setting a Key to update cascading table

Setting a Key to update cascading table

Comment

Comment

Comment

Comment

Comment

Comment