List:General Discussion« Previous MessageNext Message »
From:Miguel Vaz Date:July 12 2010 3:53pm
Subject:Re: table structure problem
View as plain text  
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@stripped> 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
>

Thread
table structure problemMiguel Vaz9 Jul
  • Re: table structure problemMySQL)12 Jul
    • Re: table structure problemMiguel Vaz12 Jul