Re: problem with table structure

On 9 Jul 2010, at 17:08, Miguel Vaz wrote:

>
> Thank you for the opinion, Alban. The names are the least of my worries, i typed them without thinking. And its portuguese. :-)
>
> If, using that design, i had a different table with something like arq_types { id_arq_type, descr } that i could somehow connect to the generic table (the one with the common fields), how could i go about querying those tables for all the results of a specific type, for example? Or maybe i could add a "table_name" field on that arq_type table?

I think you're still referring to your original design here and not to the inheritance approach, right? In that case you probably want something like:

* sites
id_site
name
description
x
y
type_site text REFERENCES site_types

* site_types
type_site text PRIMARY KEY

If you use a natural key here (like I did above) you often don't even need to join with site_types, but it does give you a constraint what people can type in the field it relates to.
Note: I'm assuming that a site can only be of one type here, I didn't have enough information to go on so that may be wrong. It gets slightly more complicated if that's the case.

If you use table inheritance you'll have to redefine the foreign key constraint on each child table, as the FK constraint won't be inherited.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c376755286211334030252!