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 <[EMAIL PROTECTED]> wrote: > At 10:52 AM 6/10/2008, [EMAIL PROTECTED] wrote: > >> Date: Tue, 10 Jun 2008 05:05:24 -0700 >> From: Bryan Emrys <[EMAIL PROTECTED]> >> To: pgsql-sql@postgresql.org >> Subject: Conceptual Design Question >> Message-ID: <[EMAIL PROTECTED]> >> >> 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
