From: Ryan <pgsql-sql@seahat.com>
To: pgsql-sql@postgresql.org
Subject: N all-way relationship
Date: Tue, 13 May 2003 09:16:31 -0500 (CDT)
Message-ID: <11523.65.102.128.233.1052835391.squirrel@fordparts.com> (raw)
OK SQL wizards, chew on this.
I am trying to visualize the best way to store and retrieve information in
a an all-way relationship with an unknown number of elements.
This is largely related to cross referencing part numbers. You have a
factory part and any number of aftermarket vendors making compatible parts
for that factory part (they are all theoretically interchangeable).
So given a factory number of 123.
if one vendor makes the part 456 that equates to 123, then, well that's
quite easy. You need a lookup table that corresponds 123 to 456, and for
reverse lookups you store 456 - 123. (you have a whopping two records)
But if you throw in a few more vendors and things get a bit more crazy.
All of a sudden parts 789, ABC and XYZ now all copy 123 (and by proxy, 456)
so you could keep doing things the structured way,
123 - 456
123 - 789
123 - ABC
123 - XYZ
456 - 123
456 - 789
456 - ABC
456 - XYZ
...
XYZ - 789
XYZ - ABC
You get the idea, you now have 20 records for five parts. Now multiply
that by the hundreds of thousands. If another vendor added a compatable
part, you just added 10 new records.
Now to the meat of my question.
Is there a better way to do this?
How could I store all this information in a single record with the unknown
number of matches, yet at any point ask for a single part and get all the
parts that would be compatible? Without breaking joins? (Each vendor has
its own price book.)
It just seems to me that there would be a more elegant solution.
Ryan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql-sql@seahat.com
Subject: Re: N all-way relationship
In-Reply-To: <11523.65.102.128.233.1052835391.squirrel@fordparts.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox