Book Database w/ Citation Tracking

[Guest]

Resident DBer's:

I'm VERY new to Access. I am trying to make a database to log and track my
research efforts (I do a LOT of R&D work). On any one topic I may read 10's
to 100's of papers, usually by following citations after citation. The paper
trail is herendous, the tree is confusing and I want something to organize
all this. SO... here's what I want to accomplish:

A database tracking papers/books recording typical information you would
normally find on a citation (Author(s), Article Title, Journal Title, pg
numbers, Pub month, pub year, publisher, volume number, issue number etc).
This isn't a huge problem for me.

The problem comes when I want to track citations. I want to keep track of
what is cited by a paper and who cites a paper. To clarify, let's say I have
a paper titled "ThePaper." It cites off 3 papers in its bibliography,
Paper1, Paper2, and Paper3. But I found ThePaper from a source (or let's say
mulitple sources for the most general case possible) that cited IT. Let's
say 2 papers cite ThePaper, we'll call them PaperA, and PaperB. Now, I want
to keep record along with The Paper all the papers it subsequently cites
(Paper1, Paper2, Paper3) and the papers that cited TO ThePaper (PaperA and
PaperB). The problems I am encountering:
If I kept track of ALL CitesTo and CitedFrom records in one field I am
limited by 255 characters - not enough. If I want a separate field for every
CitedTo and every CitedFrom then I'd have upwards of 50 fields in the CitesTo
department. (Some review papers carry with them on the order of 40-60
citations in the bibliography). SO... can I dynamically ADD more fields to
specific entries (a nightmare I am sure, and probably bad idea if possible)
OR is there a way to limitlessly log in one field a list of all citations to
and from. (By the way I don't want to just keep track of papers cited to and
from, but also THEIR paper information - but I realize I can use a key here
to just log the CitesTo and CitedFrom titles and retrieve the rest of the
info from my "papers" DB.

Any thoughts, criticisms, etc would be GREATLY appreciated.

-Jon

 

[Guest]

You MUST make a normalized data structure:
Table 1 Papers with fields paperID, Journal, author, date, title etc.
Table 2 Citations with fields paperID, paperCited. in this table you just
use the paperID numbers for both fields - they can join back to the Papers
table.

So, for your example
Papers
01,ThePaper,TheAuthor,theDate
02,PaperA,otherAuthor,otherDate
03,PaperB,otherAuthorsStudent,otherDate

Citations
01,02
01,03

Resist the temptation to use anything other than the auto-Generated keys in
the Citations table. You'll be able to create any kind of report by joining
back to the Papers table.

 

[Guest]

Oh, that hurt just thinking about a solution.

Brief thought though. Use two tables on for the book/paper information with
a unique identifier for each book (isbn?) and paper (?, not sure how you
would identify these), and a second table with citations.

First table would contain (Author(s), Article Title, Journal Title, pg
numbers, Pub month, pub year, publisher, volume number, issue number etc) and
the unique identifier.

second table contains two fields: id for book/paper and id for book/paper
cited. Each row in the table would be one citation for a particular
book/paper (the id of the book in field one and the id of the citation in
field 2). thus allowing any number of citations for each book. two queries
could be used, one to pull the citation for a book and on to pull the books
citing a particular book.

ummm hope that's somewhat clear have trouble describing what I conceptualized.

 

[Guest]

OMG... I can't believe I didn't see that! The simplest solution is always
the best!

To clarify:
One table of Books\Papers and their defining info
One table of Books\Papers with just one other that they cite
-Mulitple entries for one book - one for each book/paper it cites TO
-Multiple entries okay? do I use the main book/paper title as key?