Re: One Text Table or Multiple Text Tables - Design Tradeoffs? - Mailing list pgsql-sql

bryan.emrys@gmail.com (Bryan Emrys) writes:
> (Trying to start a new thread instead of my accidental intrusion into another thread)
>
> Hello Everyone, 
>
> In a text-heavy database, I'm trying to make an initial design decision in the following context. 
>
> There is a lot of long text that I could break down into three different categories:
>         a. Laws 
>                 i. Only 1 country per law, many laws
>                 ii. There are multiple types of laws (statutes, regulations, court cases, proposed laws, etc)
>                 iii. Each law will have only one type
>                 iv. Each law may refer to many other laws
>         b. Treaties 
>                 i. 2 countries per treaty
>                 ii. At any one time, there will only be one treaty in force between any two countries
>                 iii. There may be proposed new treaties which will supercede old treaties when finally ratified
>         c. Commentary 
>                 i. Any commentary could refer to one or more laws or treaties
>                 ii. Any commentary may have one or more authors
>                 iii. Any commentary may refer to one or more countries
>
> The conceptual question is what are the trade-offs between having
> one textual table compared with multiple text tables? Any help on
> pointing out practical considerations would be appreciated.

If there is validation that could be done as to the
inter-relationships between tuples, e.g. - there are foreign keys that
would apply to "laws" that would not apply to "treaties" or
"commentary," with the whole set of vice-versas, then I would surely
think that you'd want to have separate relations for each of these
sorts of data.

If you have multiple tables, then you can readily express validation
constraints (such as the ones you describe for each of them), and can
even have the database system enforce those constraints.

If, in contrast, you pound all the data into some single "Procrustean
bed," you'll find that you can't readily/analytically deal with the
differences between them.

A main reason to try to force all the data into a single
representation would be if you think the developers can't cope with
the complexity of the differences between the representations.  But
that's actually pretty crummy reasoning; if they can't cope with the
complexity of having per-purpose tables, then it is totally
implausible to imagine they are competent to cope properly with a
unified representation.
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://linuxfinances.info/info/spreadsheets.html
"Unless you're on  the Forbes' richest 100 list,  you're not a market,
just another photon in the rainbow."
-- Monty Brandenberg <mcbinc@ne.mediaone.net>