"Best practice" advice

From: Andrew Perrin <clists(at)perrin(dot)socsci(dot)unc(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: "Best practice" advice
Date: 2003-01-17 20:06:20
Message-ID: Pine.LNX.4.21.0301171459350.16506-100000@perrin.socsci.unc.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)perrin(dot)socsci(dot)unc(dot)edu * andrew_perrin (at) unc.edu

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2003-01-17 20:16:18 Re: "Best practice" advice
Previous Message dev 2003-01-17 15:05:27 RCF: 2nd draft: A brief guide to Nulls