Re: Conceptual Design Question - Mailing list pgsql-sql

Assuming common semantics for a given field then the question of breaking it to many parts is also a function of its
sizeas related to I/O. <br /><br />We know that memory allocation and I/O read/writes are not granular to bytes and are
ratherblocks of bytes as it travels from VM (virtual memory) all the way down to sectors on disk.<br /><br />Hence a
commonfield 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.<br /><br />Having said that, 1 Gig of RAM is about $25 at your local Cosco with a
freeslice of pizza....performance tuning paradigms are in big time flux and are really uncle Bob's war stories<br /><br
/>cheers<br/><br /><div class="gmail_quote">On Tue, Jun 10, 2008 at 11:35 AM, Steve Midgley <<a
href="mailto:public@misuse.org">public@misuse.org</a>>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> At 10:52 AM 6/10/2008,
<ahref="mailto:pgsql-sql-owner@postgresql.org" target="_blank">pgsql-sql-owner@postgresql.org</a> wrote:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"> Date: Tue, 10 Jun 2008 05:05:24 -0700<br /> From: Bryan Emrys <<a
href="mailto:bryan.emrys@gmail.com"target="_blank">bryan.emrys@gmail.com</a>><br /> To: <a
href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a><br /> Subject: Conceptual Design
Question<br/> Message-ID: <<a href="mailto:200806100505.24491.bryan.emrys@gmail.com"
target="_blank">200806100505.24491.bryan.emrys@gmail.com</a>><br/><br /> Hello Everyone,<br /><br /> In a text-heavy
database,I'm trying to make an initial design decision in the following context.<br /><br /> There is a lot of long
textthat I could break down into three different categories:<br /></blockquote> [snip]<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Theconceptual question is what are the trade-offs between having one textual table compared with multiple text tables?
Anyhelp on pointing out practical considerations would be appreciated.<br /><br /> Thanks.<br /><br /> Bryan<br
/></blockquote><br/> Hi Bryan,<br /><br /> Firstly, I might investigate the GiST index and TSearch2 in this regard. I'm
notan expert on them, and it maybe is cart before the horse, but if those tools are applicable and are easier to
implement/maintainwith one design approach or the other, I might use their design "preferences" as my guide for picking
the"right" relationships.<br /><br /> Beyond that advice, it does seem to me that a polymorphic relationship (where one
tableholds multiple entities) *could* describe laws and treaties, though they are kind of different in their relations.
Commentariesseem pretty distinct from these two things.<br /><br /> 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?<br /><br /> If you have a
unifiedORM that stores the business rules, you can be more aggressive about using polymorphism, b/c the complexity can
behidden from most developers.<br /><br /> 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
TSearch2drive your design if that's a relevant consideration. Of course TSearch2 is very flexible so it might not
reallycare much about this. :)<br /><br /> In general, I find that a data model that "looks like" the real data is the
onethat I'm happiest with - the systems I've seen with too much UML optimization and collapsing of sets of data into
singletables tend to be harder to maintain, etc.<br /><br /> Just some random opinions for you there. I'm sure others
havedifferent perspectives which are equally or more valid!<br /><br /> Best,<br /><br /> Steve<br /><font
color="#888888"><br/><br /> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"
target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br
/></font></blockquote></div><br/>