 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / March 2006Setting a Key to update cascading table
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. > Hello All, I have a DB that have 6 tables - tblCreator, > tblSummary, tblPaper, tblProposition, tblContributor, tblPanel. [quoted text clipped - 21 lines] > 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.  SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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. > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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... > I'm sorry the names of the table are confusing. tblCreator is the > creator of the proposition -- either panel or paper (i will chg [quoted text clipped - 6 lines] > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.