table structure problem

--0016e6d59e49db890f048aec0314
Content-Type: text/plain; charset=ISO-8859-1

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.

Thanks

Pag

--0016e6d59e49db890f048aec0314--

Re: table structure problem

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: table structure problem

--0016e6d7dfa3a59c24048b32c2f5
Content-Type: text/plain; charset=ISO-8859-1

Hi, Shawn,

Thanks for replying. What i meant is that i would also like to create a
table with "site types", where i would have a listing of possible sites,
like arqueology, natural, etc. and maybe use it to redirect the queries
instead of having to hardcode the table name when i need to list a specific
type of site. :-)

site_types
id_site_type
name

this table would have:
1 arqueology
2 natural
3 generic
....

how could i use this to be able to query dynamically? Maybe use the field
"id_site_type" in the table "sites" as a link?

Thanks.

Pag


PS - Shawn, forgive the duplicate email. I replied only to you first,
instead of all.



On Mon, Jul 12, 2010 at 4:37 PM, Shawn Green (MySQL) <
shawn.l.green@oracle.com> wrote:

> 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
>

--0016e6d7dfa3a59c24048b32c2f5--