Обсуждение: Address Table

Hi,

This one's any easy one I think.

Say I have a table of Parks - parks_table.

Each Parks has an address.

If each Parks has *one and one only* address
is it better to store the address in the parks_table
or build another table just for Parks addresses - parks_address ?

If I have two tables would you put the parks_table primary key in the
parks_address table or the other way round ie. the parks_address primary
key in the parks_table ?

Thanks
Regards Rudi.

Am Fre, 2003-06-27 um 01.08 schrieb Rudi Starcevic:
> If each Parks has *one and one only* address
> is it better to store the address in the parks_table
> or build another table just for Parks addresses - parks_address ?

1. more than 1 park can be at 1 address
2. name of a street can change over time

=> 2nd table for addresses

> If I have two tables would you put the parks_table primary key in the
> parks_address table or the other way round ie. the parks_address primary
> key in the parks_table ?

same like above, many parks can be at the same location.

parks_table 'gets the PRIMARY KEY' from parks_address
We speak of a so called "FOREIGN KEY" and it "REFERENCES" parks_address.

e.g.:

CREATE TABLE parks_address
(
    id_parks_address SERIAL PRIMARY KEY,
    address text NOT NULL
);

CREATE TABLE parks_table
(
    id_parks_table SERIAL PRIMARY KEY,
    id_parks_address INT REFERENCES parks_address NOT NULL,
    park_name text NOT NULL
);

INSERT INTO parks_address (address) VALUES ('foo street');
INSERT INTO parks_address (address) VALUES ('bar street');

INSERT INTO parks_table (id_parks_address, park_name) VALUES (1,
'A Park in foo');
INSERT INTO parks_table (id_parks_address, park_name) VALUES (1,
'Another Park in foo');
INSERT INTO parks_table (id_parks_address, park_name) VALUES (2,
'A Park in bar');

If you want to DELETE all parks automatically when an address no longer
exists (e.g. an earthquake :) then you should write

REFERENCES parks_address ON DELETE CASCADE NOT NULL

instead.

If you mean, it's impossible for an address to disappear suddenly :)

then you could write:

REFERENCES parks_address ON DELETE RESTRICT NOT NULL

AFAIK this is the default.

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Hi,

Just one other thing.
With the senario below it's possible to have addresses without a link to any Park.

Say if I remove a Park then it's row in the parks_address table still exists.

I guess I just need to write a script to look for lonely addresses ?

Does that sound OK ?

Cheers
Rudi.



Nabil Sayegh wrote:

Am Fre, 2003-06-27 um 01.08 schrieb Rudi Starcevic: 

If each Parks has *one and one only* address
is it better to store the address in the parks_table
or build another table just for Parks addresses - parks_address ?   

1. more than 1 park can be at 1 address
2. name of a street can change over time

=> 2nd table for addresses
 

If I have two tables would you put the parks_table primary key in the
parks_address table or the other way round ie. the parks_address primary 
key in the parks_table ?   

same like above, many parks can be at the same location.

parks_table 'gets the PRIMARY KEY' from parks_address
We speak of a so called "FOREIGN KEY" and it "REFERENCES" parks_address.

e.g.:

CREATE TABLE parks_address
(id_parks_address SERIAL PRIMARY KEY,address text NOT NULL
);

CREATE TABLE parks_table
(id_parks_table SERIAL PRIMARY KEY,id_parks_address INT REFERENCES parks_address NOT NULL,park_name text NOT NULL
);

INSERT INTO parks_address (address) VALUES ('foo street');
INSERT INTO parks_address (address) VALUES ('bar street');

INSERT INTO parks_table (id_parks_address, park_name) VALUES (1,
'A Park in foo');
INSERT INTO parks_table (id_parks_address, park_name) VALUES (1,
'Another Park in foo');
INSERT INTO parks_table (id_parks_address, park_name) VALUES (2,
'A Park in bar');

If you want to DELETE all parks automatically when an address no longer
exists (e.g. an earthquake :) then you should write

REFERENCES parks_address ON DELETE CASCADE NOT NULL

instead.

If you mean, it's impossible for an address to disappear suddenly :)

then you could write:

REFERENCES parks_address ON DELETE RESTRICT NOT NULL

AFAIK this is the default.

HTH 

Rudi,

> If each Parks has *one and one only* address
> is it better to store the address in the parks_table
> or build another table just for Parks addresses - parks_address ?

If each Park has exaclty one address, just put the address in the "parks"
table.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Am Fre, 2003-06-27 um 02.26 schrieb Rudi Starcevic:
> Hi,
>
> Just one other thing.
> With the senario below it's possible to have addresses without a link
> to any Park.
>
> Say if I remove a Park then it's row in the parks_address table still
> exists.

It isn't "it's" row.
It doesnt belong to this 1 park.
There could be another park which uses it (maybe in future).

I would kust let it stay there.

First of all, to make it clearer, I wouldn't call the table:

parks_address

Because this is misleading. The Table is just an

address

table. It doesnt store any information about parks. There could be many
more tables which 'use' addresses (Cinemas, Restaurants).


> I guess I just need to write a script to look for lonely addresses ?
>
> Does that sound OK ?

Well, I personally wouldn't. But of course you could.

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de