Thread: many junction tables
I was wondering if anyone has a solution to the following problem. I have a few tables similar to the following. person_table city_table company_table If I want to associate a person with a one or more cities. I need to create a city_persons table that contains just the primary keys of both the city_table and person_table. If I want to associate a person to many companies, I have to do a similar thing. Now, if I wan to generalize these associaes I can create something like an associate table which will contain both id's plus two columns which indicate the source and target tables of the relaionship. Does anyone have suggestions along these lines on how to generalize such relationships? thanks, --eric
On Tue, Sep 04, 2001 at 07:52:59AM -0700, Eric Kolve wrote: > I was wondering if anyone has a solution to the following problem. I > have a few tables similar to the following. > > person_table > > city_table > > company_table > > If I want to associate a person with a one or more cities. I need to > create a city_persons table that contains just the primary keys of both > the city_table and person_table. If I want to associate a person to > many companies, I have to do a similar thing. Now, if I wan to > generalize these associaes I can create something like an associate > table which will contain both id's plus two columns which indicate the > source and target tables of the relaionship. Does anyone have > suggestions along these lines on how to generalize such relationships? Other people will no doubt give better answers, but: The generalisation you describe is not really possible in SQL, since you're not allowed to use data in places where table names are required. You could fake it with functions however. Secondly, it's not really clear that generalisation in that is really useful. It doesn't help performance, It's certainly not clear what's going on. And you won't find what you describe in any book on database design. it's simply not done. The only case I know where you may link to different tables is in the case of inheritance. But that's a clearly defined idea with constraints as to what is possible. Maybe you can describe what you are trying to acheive (if there is more to that what is described above). > > thanks, > > --eric > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
I don't understand what exactly your problem is. What what makes me unsure is: why do you want to store the target table names in the association table? Eric Kolve wrote: > > I was wondering if anyone has a solution to the following problem. I > have a few tables similar to the following. > > person_table > > city_table > > company_table > > If I want to associate a person with a one or more cities. I need to > create a city_persons table that contains just the primary keys of both > the city_table and person_table. If I want to associate a person to > many companies, I have to do a similar thing. Now, if I wan to > generalize these associaes I can create something like an associate > table which will contain both id's plus two columns which indicate the > source and target tables of the relaionship. Does anyone have > suggestions along these lines on how to generalize such relationships? > > thanks, > > --eric > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
The idea is this. Suppose I had say 40 or 50 tables like city or company (schools, purchases, etc) where I want to have a one to many relationship (One person can purchase many things, one person can attend many schools) I would have to create many junction tables (school_persons, purchase_persons, etc). What I am looking for is a solution to creating all these tables, if at all possible. --eric Arne Weiner wrote: > > I don't understand what exactly your problem is. What > what makes me unsure is: why do you want to store the > target table names in the association table? > > Eric Kolve wrote: > > > > I was wondering if anyone has a solution to the following problem. I > > have a few tables similar to the following. > > > > person_table > > > > city_table > > > > company_table > > > > If I want to associate a person with a one or more cities. I need to > > create a city_persons table that contains just the primary keys of both > > the city_table and person_table. If I want to associate a person to > > many companies, I have to do a similar thing. Now, if I wan to > > generalize these associaes I can create something like an associate > > table which will contain both id's plus two columns which indicate the > > source and target tables of the relaionship. Does anyone have > > suggestions along these lines on how to generalize such relationships? > > > > thanks, > > > > --eric > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Oh, Ok. Now I understand. It seems that there is no solution using SQL without using inheritance. I had some Ideas but postgres has weird sematics (not conforming SQL99) using inheritance and so it didn't work. But you should not - as you suggested - store the tablenames in a "assocciation" table. That is no clean database design. If I can get my ideas running, I'll post you. Arne. E Kolve wrote: > > The idea is this. Suppose I had say 40 or 50 tables like city or > company (schools, purchases, etc) where I want to have a one to many > relationship (One person can purchase many things, one person can attend > many schools) I would have to create many junction tables > (school_persons, purchase_persons, etc). What I am looking for is a > solution to creating all these tables, if at all possible. > > --eric > > Arne Weiner wrote: > > > > I don't understand what exactly your problem is. What > > what makes me unsure is: why do you want to store the > > target table names in the association table? > > > > Eric Kolve wrote: > > > > > > I was wondering if anyone has a solution to the following problem. I > > > have a few tables similar to the following. > > > > > > person_table > > > > > > city_table > > > > > > company_table > > > > > > If I want to associate a person with a one or more cities. I need to > > > create a city_persons table that contains just the primary keys of both > > > the city_table and person_table. If I want to associate a person to > > > many companies, I have to do a similar thing. Now, if I wan to > > > generalize these associaes I can create something like an associate > > > table which will contain both id's plus two columns which indicate the > > > source and target tables of the relaionship. Does anyone have > > > suggestions along these lines on how to generalize such relationships? > > > > > > thanks, > > > > > > --eric > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please send an appropriate > > > subscribe-nomail command to majordomo@postgresql.org so that your > > > message can get through to the mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Tue, Sep 04, 2001 at 10:22:19AM -0700, E Kolve wrote: > The idea is this. Suppose I had say 40 or 50 tables like city or > company (schools, purchases, etc) where I want to have a one to many > relationship (One person can purchase many things, one person can attend > many schools) I would have to create many junction tables > (school_persons, purchase_persons, etc). What I am looking for is a > solution to creating all these tables, if at all possible. Well, for one-to-many relationships you don't need separate tables. Only many-to-many relationships need that. And most of the time a many-to-many relationship needs more than just the two fields. For example, students and classes have a many-to-many relationship, but just a simple two column table is not enough, what you really need it a new enrolment table which has the two fields and also any other information related (like grades, etc). Since you only create the schema once, you can just generate a script to create them for you. Yes, you'll get a lot of tables, but it's a complex data structure. So in the end all you have is one-many relationships. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.