Thread: "Best practice" advice
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
Andrew, > 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: I'd suggest, instead, having just one "documents" table with a boolean column called "is_core". This will simplify relating core docs to each other, and additionally allow for the promotion of "peripheral" docs to the core easily. -- -Josh BerkusAglio Database SolutionsSan Francisco