Date: Tue, 13 May 2003 11:38:14 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Ryan <pgsql-sql@seahat.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: N all-way relationship
Message-ID: <20030513163814.GA19817@wolff.to>
Mail-Followup-To: Ryan <pgsql-sql@seahat.com>,
	pgsql-sql@postgresql.org
References: <11523.65.102.128.233.1052835391.squirrel@fordparts.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <11523.65.102.128.233.1052835391.squirrel@fordparts.com>
User-Agent: Mutt/1.5.4i

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.