Re: Conceptual Design Question

Assuming common semantics for a given field then the question of breaking it
to many parts is also a function of its size as related to I/O.

We know that memory allocation and I/O read/writes are not granular to bytes
and are rather blocks of bytes as it travels from VM (virtual memory) all
the way down to sectors on disk.

Hence a common field of say 2000 bytes will most likely cause multiple I/O
requests where application layer did not have any use for 80% of it, 80%
of the times.

Having said that, 1 Gig of RAM is about $25 at your local Cosco with a free
slice of pizza....performance tuning paradigms are in big time flux and are
really uncle Bob's war stories

cheers

On Tue, Jun 10, 2008 at 11:35 AM, Steve Midgley <public(at)misuse(dot)org> wrote:

> 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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>