Thread: Address Table

Address Table

From
Rudi Starcevic
Date:
Josh,

>> But if addresses are dependant on parks, you simply use ON DELETE
>> CASCADE in your FK declaration, and when you delete a Park its addresses will
>> be automatically deleted.

I think I can only do this is I have the parks_table key in the parks_address table.

One other option, as Nabil suggested, is to have the foreign key the other way round.
Ie. the parks_address key in the parks_table.

Using Nabil's suggestion I wouldn't be able to cascade the delete into the parks_address table.
Thus leaving a lonely address.

So far I think we have discussed 3 solid and sensible options which are all correct SQL possibilities.

I'm just trying to get clear in my head which is the best for this senario.

Thanks
Rudi.






Re: Address Table

From
Josh Berkus
Date:
Rudi,

> One other option, as Nabil suggested, is to have the foreign key the other
> way round. Ie. the parks_address key in the parks_table.

No offense to Nabil, but I can't see a good reason to employ that design.
He's suggesting sort of a "backwards key", which could cause all kinds of
headaches.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Address Table

From
Nabil Sayegh
Date:
Am Fre, 2003-06-27 um 05.10 schrieb Josh Berkus:
> Rudi,
>
> > One other option, as Nabil suggested, is to have the foreign key the other
> > way round. Ie. the parks_address key in the parks_table.
>
> No offense to Nabil, but I can't see a good reason to employ that design.
> He's suggesting sort of a "backwards key", which could cause all kinds of
> headaches.

My assumption was (and still is): that at 1 address there could be N
objects (not only parks, imagine a mall with several shops, restaurants,
cinemas)

But if you're sure that each address is unique to 1 address ...

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


Re: Address Table

From
Nabil Sayegh
Date:
Am Fre, 2003-06-27 um 03.09 schrieb Rudi Starcevic:

> I'm just trying to get clear in my head which is the best for this senario.

1. If someday you also want to store information about cinemas you'll
have the same problem again. Now you have 2 tables which contain
addresses

2. After having 42 tables that all store information about addresses you
come to the clever idea that you could store a routemap to that address
:)

3. Now you have to ALTER 42 tables to also store information about
routemaps.

4. You have to store the same routemap for n parks/...

But there are many solutions to a problem, and if you say my solution is
overkill to your problem, you could also pick an other solution.

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


Re: Address Table

From
Josh Berkus
Date:
Nabil,

> My assumption was (and still is): that at 1 address there could be N
> objects (not only parks, imagine a mall with several shops, restaurants,
> cinemas)

Aha.  That makes more sense.

Confused, yet, Rudi?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Address Table

From
Nabil Sayegh
Date:
Am Fre, 2003-06-27 um 03.09 schrieb Rudi Starcevic:

> I'm just trying to get clear in my head which is the best for this senario.

1. If someday you also want to store information about cinemas you'll
have the same problem again. Now you have 2 tables which contain
addresses

2. After having 42 tables that all store information about addresses you
come to the clever idea that you could store a routemap to that address
:)

3. Now you have to ALTER 42 tables to also store information about
routemaps.

4. You have to store the same routemap for n parks/...

But there are many solutions to a problem, and if you say my solution is
overkill to your problem, you could also pick an other solution.

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