Skip site navigation (1) Skip section navigation (2)

Re: arc relationship [was: db design question]

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: jules(dot)alberts(at)arbodienst-limburg(dot)nl, pgsql-novice(at)postgresql(dot)org
Subject: Re: arc relationship [was: db design question]
Date: 2002-10-23 16:39:49
Message-ID: web-1796563@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
Jules,

> Why the necessity to have a row in the child table for each row in
> the 
> parent? Only when customer has an address, I will insert a row in the
> 
> child. So the child table will be only as large as the sum of
> addresses 
> required for all parents together, not as large as the sum of all
> rows 
> in all parents together. Maybe I didn't understand you correctly 
> (English isn't my native language)

1) I am not reccomending that you use this approach for addresses --
the standard relational model will serve your purposes, so there's not
reason to get creative.  I was reccomending that you try the "flexible
child" approach *only* for the BLOB reference table.

2) In answer to your question:  Imagine that I have 5 tables, clients,
employees, invoices, orders, and payments.   Imagine that each table
has roughly 20,000 rows.   Each table also has a row in the "mod_data"
table.  If I want to query the mod_data for a particular client, then
the database has to search 100,000 rows, not the 20,000 it would search
if the mod_data were directly in the clients table.   get it?

> Do you have an URL to such systems? I'm not familiar with them, I
> guess 
> you don't mean journalling filesystems? TIA!

I'm talking about triggers or other mechanisms that record each change
to the database records into a permanent archive for auditing purposes.

> With the info I have so far, I plan to work like this:

See above.  As I said before, I feel that the "flexible child" approach
is a *bad* approach for storing the addresses.   I just suggested it
for the BOLBs.   For the addresses, see my first e-mail to you on the
topic.

-Josh Berkus

In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-10-23 18:06:33
Subject: San Francisco Bay Area PostgreSQL User's Group
Previous:From: Ludwig LimDate: 2002-10-23 16:13:20
Subject: Re: Re[1] Display SELECT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group