Re: problem with table structure

On 9 Jul 2010, at 3:41, Miguel Vaz wrote:

> and i would like to put these two "sites" in the same data set and maybe add a new table called "site types" to categorize each record (maybe a relation table to allow many to many): how can i go about doing it? is this solution decent enough:
>
> * sites (generic):
>
> id_site
> name
> description
> x
> y
>
>
> * site_natural
> id
> id_site
> altitude
>
> * site_arqueology
> id
> id_site
> id_category
> id_period
>
> But i seem to be missing something. How can i have this in a way that its easy to list only "arqueology sites" for example. I feel the solution is simple enough, even for me, but its eluding me. Any help in the right direction would be very appreciated.

That design seems fine to me. I'd probably put unique constraints on site_natural.id_site and site_arqueology.id_site to fix those into a 1:1 relationship with site.id, otherwise it would allow data with a 1:n relationship - multiple natural or archeological sites at the same location and name.

Another possibility is to use table inheritance, but be aware that some things can't be inherited (foreign key constraints, for example).

BTW, shouldn't that table be named site_archeological? Or if arquelogy is Spanish or Portuguese, shouldn't it be arquelogical?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4c370742286211218711353!