I'm developing the second stage of a database that will eventually be used
to model networks of references between documents. I already have a table
of core documents, and the next step is to track what documents each of
these core documents refers to. (Is this confusing enough already?)
The relationship is one-to-many, and I can handle that fine. The issue
is: some of the references in the core documents will be to other core
documents. Other references will be to documents that are not in the core
documents table. I need to track whether the document referred to is
in the core table or not. The question is how best to capture this. Ideas
I have are:
1.) A single referrals table that can track both kinds:
referring_id --> serial number of the referring core document
referred_title
referred_author
referred_date
referred_page
referred_id --> serial number of the referred document if it's in the core table; otherwise
NULL
2.) Two referrals tables:
referring_id referring_id
referred_title referred_id
referred_author
referred_date
referred_page
3.) A "peripheral documents" table and a referrals table:
periph_id referring_id
title referred_table
author referred_id
date
page
Comments?
Thanks.
----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu