Re: many junction tables

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout <kleptog(at)svana(dot)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.