Обсуждение: problem with table structure
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
Miguel Vaz <pagongski@gmail.com> wrote: > [...] > * 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 mean archaeological sites that are not also natural sites? | SELECT * FROM site_arqeuology | WHERE id_site NOT IN | (SELECT id_site FROM site_natural); There are numerous other ways to do this, i. e., with "LEFT JOIN", "EXCEPT", etc. Tim
Hi, Tim,
I was looking for an opinion on the actual table structure. :-) How should i build the data set? Is my second example ok? The first is the long version but i wanted to put together all the common fields to both types of "sites" and then (maybe) build tables to accomodate the specific fields so there are no empty columns on the table if i put everything in the same table.
Pag
On Fri, Jul 9, 2010 at 10:30 AM, Tim Landscheidt <tim@tim-landscheidt.de> wrote:
Miguel Vaz <pagongski@gmail.com> wrote:
> [...]> * sites (generic):You mean archaeological sites that are not also natural
> 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.
sites?
| SELECT * FROM site_arqeuology
| WHERE id_site NOT IN
| (SELECT id_site FROM site_natural);
There are numerous other ways to do this, i. e., with "LEFT
JOIN", "EXCEPT", etc.
Tim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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!
Miguel Vaz <pagongski@gmail.com> wrote: > I was looking for an opinion on the actual table structure. :-) How should i > build the data set? Is my second example ok? The first is the long version > but i wanted to put together all the common fields to both types of "sites" > and then (maybe) build tables to accomodate the specific fields so there are > no empty columns on the table if i put everything in the same table. > [...] That's way too fuzzy for good advice. Few people build data sets on archaeological sites, and even those probably don't use all the same structure. Tim (not telepathic)
Thank you for the opinion, Alban. The names are the least of my worries, i typed them without thinking. And its portuguese. :-)
If, using that design, i had a different table with something like arq_types { id_arq_type, descr } that i could somehow connect to the generic table (the one with the common fields), how could i go about querying those tables for all the results of a specific type, for example? Or maybe i could add a "table_name" field on that arq_type table?
Tim:
Dont consider this to be strictly for archeology, i mean in a generic sense that if we have several data sets with common fields, if we could divide them into several tables, one with common fields, and the others with fields related to each type. My doubt was regarding how to have a separate table with "types" that could be used to help query the "common fields table" and fetch the corresponding table of that specific type. I understand its a bit ungrateful for you guys to understand what i mean, considering that i am probably making things even more confusing. :-)
Pag
On Fri, Jul 9, 2010 at 1:47 PM, Tim Landscheidt <tim@tim-landscheidt.de> wrote:
> I was looking for an opinion on the actual table structure. :-) How should i> [...]
> build the data set? Is my second example ok? The first is the long version
> but i wanted to put together all the common fields to both types of "sites"
> and then (maybe) build tables to accomodate the specific fields so there are
> no empty columns on the table if i put everything in the same table.
That's way too fuzzy for good advice. Few people build data
sets on archaeological sites, and even those probably don't
use all the same structure.
Tim
(not telepathic)
--Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 9 Jul 2010, at 17:08, Miguel Vaz wrote: > > Thank you for the opinion, Alban. The names are the least of my worries, i typed them without thinking. And its portuguese.:-) > > If, using that design, i had a different table with something like arq_types { id_arq_type, descr } that i could somehowconnect to the generic table (the one with the common fields), how could i go about querying those tables for allthe results of a specific type, for example? Or maybe i could add a "table_name" field on that arq_type table? I think you're still referring to your original design here and not to the inheritance approach, right? In that case youprobably want something like: * sites id_site name description x y type_site text REFERENCES site_types * site_types type_site text PRIMARY KEY If you use a natural key here (like I did above) you often don't even need to join with site_types, but it does give youa constraint what people can type in the field it relates to. Note: I'm assuming that a site can only be of one type here, I didn't have enough information to go on so that may be wrong.It gets slightly more complicated if that's the case. If you use table inheritance you'll have to redefine the foreign key constraint on each child table, as the FK constraintwon't be inherited. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c376755286211334030252!
Miguel Vaz <pagongski@gmail.com> wrote: > Thank you for the opinion, Alban. The names are the least of my worries, i > typed them without thinking. And its portuguese. :-) > If, using that design, i had a different table with something like arq_types > { id_arq_type, descr } that i could somehow connect to the generic table > (the one with the common fields), how could i go about querying those tables > for all the results of a specific type, for example? Or maybe i could add a > "table_name" field on that arq_type table? > Tim: > Dont consider this to be strictly for archeology, i mean in a generic sense > that if we have several data sets with common fields, if we could divide > them into several tables, one with common fields, and the others with fields > related to each type. My doubt was regarding how to have a separate table > with "types" that could be used to help query the "common fields table" and > fetch the corresponding table of that specific type. I understand its a bit > ungrateful for you guys to understand what i mean, considering that i am > probably making things even more confusing. :-) > [...] I think the main problem is that you haven't stated your ex- perience with SQL (or databases in general). Your questions above ("somehow connect to the generic table", "go about querying those tables") indicate that you seem to be lacking basic knowledge. In this case, it won't help you, us or your database to ask how to structure your data; you should read a tutorial, and then choose a structure that you understand and that works for you. But at the moment, you're basically saying: "I'd like to build a vehicle; I haven't decided yet whether it should take me to the next pub or the moon. Which screws should I use?" Tim
Tim: ah, come on. :-P
I do have basic knowledge, and beyond. I am mostly a MySQL dev (dont flame yet), but have a good grasp on bds in general.
I usually solve the BD problems/situations in a way i can easily code around it, since i am normally the dev on the programming front also. This time i am building the BD for someone else, with certain constraints, so my question was more of a general use, or common procedure - if there is one - for that particular problem.
I understand that you, Tim, might be more pragmatic about the whole matter, and please forgive my use of english, which is also not my native language, but its merely what i stated above: i do have knowledge, i can solve the stated situation in a way *I* could work with, but thought of asking you, master SQL'ers, about how you would solve the situation. Just that, no strings attached, no complications needed. :-)
Email is a very ungrateful media for db explanations. It easily becomes boring and extensive, for both the writer and the reader. Still, thanks for the help, guys. :-)
As for the vehicle, Tim, i would prefer the pub, since at the moon is where i usually spend my days, hehe.
Pag
On Fri, Jul 9, 2010 at 7:16 PM, Tim Landscheidt <tim@tim-landscheidt.de> wrote:
> Thank you for the opinion, Alban. The names are the least of my worries, i> [...]
> typed them without thinking. And its portuguese. :-)
> If, using that design, i had a different table with something like arq_types
> { id_arq_type, descr } that i could somehow connect to the generic table
> (the one with the common fields), how could i go about querying those tables
> for all the results of a specific type, for example? Or maybe i could add a
> "table_name" field on that arq_type table?
> Tim:
> Dont consider this to be strictly for archeology, i mean in a generic sense
> that if we have several data sets with common fields, if we could divide
> them into several tables, one with common fields, and the others with fields
> related to each type. My doubt was regarding how to have a separate table
> with "types" that could be used to help query the "common fields table" and
> fetch the corresponding table of that specific type. I understand its a bit
> ungrateful for you guys to understand what i mean, considering that i am
> probably making things even more confusing. :-)
I think the main problem is that you haven't stated your ex-
perience with SQL (or databases in general). Your questions
above ("somehow connect to the generic table", "go about
querying those tables") indicate that you seem to be lacking
basic knowledge. In this case, it won't help you, us or your
database to ask how to structure your data; you should read
a tutorial, and then choose a structure that you understand
and that works for you.
But at the moment, you're basically saying: "I'd like to
build a vehicle; I haven't decided yet whether it should
take me to the next pub or the moon. Which screws should I
use?"
Tim
--Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general