Thread: Address Table
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.
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
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
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
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
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