Thread: N all-way relationship

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

--- Ryan <pgsql-sql@seahat.com> wrote:
> This is largely related to cross referencing part numbers

Why not
table master_part(  part_id int primary key, part_no not null unique, ... );
table vendor( vendor_no primary key, ... );
table vendor_part( part_id int primary key default(seq), vendor_no references vendor, master_part references
master_part(part_id ), vendor_part, constraint unique( vendor_no, vendor_part ), ... );
 

Everything here is nice and normal (use part_id to avoid compound
primary keys).  Then use views for all cross referencing.



__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

On Tue, May 13, 2003 at 09:16:31 -0500, Ryan <pgsql-sql@seahat.com> wrote:
> 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)

Because the relationship is reflexive I don't think you want to store
two records for what is one relationship.

> 
> 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?

I think what you want to do is have a table of your parts, and a table
of what other companies parts are equivalent to your parts. You don't
need to store which other companies' parts are equivalent to each other
since that is derivable. You might want to include the identity rows
where your parts are related to themselves to make some queries simpler.

> 
> 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.)

You don't want to do that. You can get the answer to this kind of question
by getting all of the other companies' parts that are equivalent to your
companies' part that is equivalent to the original part being asked about.

> 
> It just seems to me that there would be a more elegant solution.

On Tue, 13 May 2003, Ryan wrote:

> 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.
> 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)

> 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?

Why wouldn't you do a self reference for storing this?

create table ( id serial not null unique, partnum int, equiv_part int );

Your factory part number 123 goes in as, say (1, 123, NULL ).
All equivevalent parts go in as (2, 456, 1), (3, ABC, 1), etc.

Or am I missing something?

-j



-- 
Jamie Lawrence                                        jal@jal.org
"Every duck should aspire to be crispy and aromatic."   -sleepyhel