On 7/8/2010 11:29 PM, Miguel Vaz wrote:
> Hi,
>
> I am having some uncertainty while designing the following structure:
>
> I have two sets of data:
>
> * arqueology sites (can be natural):
>
> id
> name
> description
> id_category
> id_period
> x
> y
>
>
> * natural sites (can be arqueological also - bear with me -, so there will
> be duplicate records in the above table and this):
>
> id
> name
> description
> altitude
> x
> y
>
> 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.
>
You have done a nice job at normalizing. All of you sites are tagged and
identified in one table then specific differentiating details are stored
in separate tables.
When you ask about listing only the "arqueology sites" that's just a
simple query:
SELECT * from site_arqueology inner join sites on sites.id_site =
site_arqueology.id_site
I can't see where your problem is. Can you provide some additional details?
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
