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
categorizeeach record (maybe a relation table to allow many to many): how can i go about doing it? is this solution
decentenough: 
>
> * 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
veryappreciated. 

That design seems fine to me. I'd probably put unique constraints on site_natural.id_site and site_arqueology.id_site
tofix those into a 1:1 relationship with site.id, otherwise it would allow data with a 1:n relationship - multiple
naturalor 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!