Re: Conceptual Design Question

At 10:52 AM 6/10/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Tue, 10 Jun 2008 05:05:24 -0700
>From: Bryan Emrys <bryan(dot)emrys(at)gmail(dot)com>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: Conceptual Design Question
>Message-ID: <200806100505(dot)24491(dot)bryan(dot)emrys(at)gmail(dot)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