Re: arc relationship [was: db design question]

On 22 Oct 2002 at 9:36, Josh Berkus wrote:

Hello Josh,

> Getting back to Jules' original question about having a universal
> "blob" table, as I said, I've done this. An example:
<snip>
> Both of the above tables, through the ref_usq, are related to any of
> the tables possessing a USQ as the primary key. This is in use in a
> production system, and has been quite reliable.

That's good to hear!

<snip>
> 2) Any "flexible child" table has, of necessity, as many rows as the
> sum total of the rows in the parent tables, or the requisite multiple
> for one-to-many relations. This results in a child table that is much,
> much larger than the standard model of having several different child
> tables would be. As I have previously mentioned, this is a
> performance drag as one large table is, in practice, slower than
> several small tables.

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)

> 3) Any auto-journaling system or auto-archiving system of record
> changes will have difficulty working around the above ambiguous
> relationships.
<snip>

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

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

------------------------------------------------------
-- sequence for global primary key
create sequence glob_sq;

-- the parents
create table customer (
glob_id int unique default nextval('glob_sq'),
id serial primary key,
name varchar);

create table employee (
glob_id int unique default nextval('glob_sq'),
id serial primary key,
name varchar);

-- one of the children
create table address (
-- tables that have no children need no global ID
id serial primary key,
ref_table varchar,
ref_id int not null,
street varchar);

-- some testing, create some parents
insert into employee (name) values ('Paul');
insert into employee (name) values ('John');
insert into customer (name) values ('Apple Inc.');
select * from employee;
select * from customer;

-- create some children (that's the nice part :-)
insert into address (ref_table, ref_id, street) values (
'employee',
(select glob_id from employee where name = 'Paul'),
'Penny Lane');
insert into address (ref_table, ref_id, street) values (
'customer',
(select glob_id from customer where name = 'Apple Inc.'),
'Abbey Road');
select * from address;

-- search Paul's address
select street from address where ref_id =
(select glob_id from employee where name = 'Paul');

-- now backward, find the employee that lives on Penny Lane. If done
-- in real life, the FROM table would be generated in runtime with a
-- SELECT REF_TABLE FROM ADDRES WHERE ...Thas' where the ref_table
-- column comes in handy
select name from employee where glob_id =
(select glob_id from address where street = 'Penny Lane');
------------------------------------------------------