Re: Conceptual Design Question

At 10:52 AM 6/10/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Tue, 10 Jun 2008 05:05:24 -0700
>From: Bryan Emrys <bryan.emrys@gmail.com>
>To: pgsql-sql@postgresql.org
>Subject: Conceptual Design Question
>Message-ID: <200806100505.24491.bryan.emrys@gmail.com>
>
>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:
[snip]
>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.
>
>Thanks.
>
>Bryan

Hi Bryan,

Firstly, I might investigate the GiST index and TSearch2 in this 
regard. I'm not an expert on them, and it maybe is cart before the 
horse, but if those tools are applicable and are easier to 
implement/maintain with one design approach or the other, I might use 
their design "preferences" as my guide for picking the "right" 
relationships.

Beyond that advice, it does seem to me that a polymorphic relationship 
(where one table holds multiple entities) *could* describe laws and 
treaties, though they are kind of different in their relations. 
Commentaries seem pretty distinct from these two things.

My overall opinion would also depend on the architecture. Will you have 
a unified middleware/ORM layer that can manage the business rules for 
the polymorphic data retrieval? Or will developers be going directly 
into the database to pull items directly?

If you have a unified ORM that stores the business rules, you can be 
more aggressive about using polymorphism, b/c the complexity can be 
hidden from most developers.

All in all, I think your model is really describing three distinct data 
entities, and should be stored in three separate tables, but that's a 
very high level and uninformed opinion! I'd let TSearch2 drive your 
design if that's a relevant consideration. Of course TSearch2 is very 
flexible so it might not really care much about this. :)

In general, I find that a data model that "looks like" the real data is 
the one that I'm happiest with - the systems I've seen with too much 
UML optimization and collapsing of sets of data into single tables tend 
to be harder to maintain, etc.

Just some random opinions for you there. I'm sure others have different 
perspectives which are equally or more valid!

Best,

Steve