Re: db design question

On Tue, 2002-10-22 at 20:14, Jules Alberts wrote:
> On 21 Oct 2002 at 8:44, Josh Berkus wrote:
>
> So let's say I don't use OID. Is a SEQUENCE bound to several tables the
> best solution? Any other suggestions?

The sequence doesn't have to be bound to several tables - just your
address table:

Josh originally said:
> That's a fine idea, except that you have the referential integrity
> backward:
>
> Company(name varchar(100), address_id INT)
> employee(code int, address_id INT)
> consultant(name varchar(50), address_id INT)
> address(address_id INT PRIMARY KEY, street varchar(100), state
> varchar(100))

My only quibble with this would be to change the address table thus:

address( address_id SERIAL PRIMARY KEY, street TEXT, state TEXT );

This will create a sequence for you called address_address_id_seq and
set the default to nextval('address_address_id_seq') so that whenever
you create a new record without specifically assigning a sequence, it
will get handed one.

When writing a company record you do something like:

BEGIN;
addr_id = "SELECT nextval('address_address_id_seq');

insert into company (name, address_id ) values('company name', addr_id
);

insert into address( address_id, street, state ) values( addr_id,
'George Street', 'New South Wales');
COMMIT;

Obviously similar things happen writing an employee record or whatever.

> a one to many relationship where "one" can be any table in the db
>
> How does one solve this in a relational model? Sorry if I keep going on
> about this, but I have a hunch that this is _very_ important for my db.
> If I don't solve it correctly, I'm sure I will be in a lot of trouble
> later on.

It isn't at all unusual, I'm afraid. Codes tables do this sort of thing
all the time - where you have a table that contains:
Code Value
M Male
F Female
X Unknown

And you want to refer to that in all sorts of places in the system.
Well, in the places you want to refer to it you store the unique
identifier, i.e. the "Code".

For your case, you want to store the unique identifier (i.e.
address_id).

> In this way, I can store all the addresses together and find them
> with.
> SELECT * WHERE addres.ref_oid = company.oid;

This turns around and becomes something like:

SELECT * FROM address a, company c WHERE a.address_id = c.address_id ;

Cheers,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for free with http://survey.net.nz/
---------------------------------------------------------------------