Re: [ic] One table, multiple vendors, product codes not unique

[cfm@maine.com]

On Tue, Jun 12, 2001 at 11:09:29AM -0400, Mike Heins wrote:
> Quoting cfm@maine.com (cfm@maine.com):
> > 
> > Re: One table, multiple vendors, product codes not unique
> > 
> > We've got a mall scenario with multiple vendors in same
> > tables and therefore we commonly have more than one
> > instance of an sku, say '1000'.
> > 
> > The unique key is not (sku) but instead it is (sku,id), 
> > where id is the vendor number and guaranteed
> > to be there.  We are using a custom price routine for 
> > now but would like to get rid of that for mainstream.
> > 
> > We assign our own unique keys and sku's but the mall 
> > vendors need to be able to link in with their own like:
> >   vlink.cgi/1234?merchant=my_store
> > And JOINs need to reference their codes.
> > 
> > Can newer versions (>4.03) stock mv/ic handle that?
> > 
> > I'm tempted to modify Table/DBI.pm to always include the
> > id key in queries or maybe to use Watch Points.  Can 
> > anyone share any thoughts on this?
> 
> Not really -- we would need specifics on what you are talking
> about with JOINS, and what access you need.  I am afraid I
> still cannot figure out what you want from this.

For example:
sku        product      price    id    merchant
1000       Shirt        10.00    007   goldfinger
1000       Balloon      50.00    2     prisoner
1000       Towel        500.00   42    zaphod

In the simplest case, I'd like [item_price] to return
10.00 for (1000,007), 50.00 for (1000,2) and
500.00 for (1000,42).

Sort of like what one might expect this (not functional)
CommonAdjust string to do:
table*:column:(key1,key2)
products:price:(code,id)

The JOINS are only an issue, like the link, because the
vendors need to use **their** codes.  For example

SELECT * FROM products AS P,swatches AS S WHERE P.sku=S.sku AND P.id=S.id
prevents the colors of the shirts, towels and balloons
getting mixed up because sku is not unique.  As opposed to

SELECT * FROM products AS P,swatches AS S WHERE P.code=S.code;
where code is our number that looks like '20010530061555'
and is not very meaningful to vendor.

Furthermore, vlink.cgi/20010530061555 might work today 
but it might well not work tomorrow.  Better for vendor 
to use vlink.cgi/1234?merchant=my_store.

Anyway, I think I have my answer - unknown territory! :-)

cfm
> 
> 4.7.3 has a foreign-key capability, but that is pointed toward
> accessing a single data item keyed via a non-primary key field.
> 
> You can do quite a bit with [query sql="...." list=1]. That may be
> what you want.
> 
> -- 
> Red Hat, Inc., 3005 Nichols Rd., Hamilton, OH  45013
> phone +1.513.523.7621      <mheins@redhat.com>
> 
> I have a cop friend who thinks he ought be able to give a new ticket;
> "too dumb for conditions".
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users

-- 

Christopher F. Miller, Publisher                             cfm@maine.com
MaineStreet Communications, Inc         208 Portland Road, Gray, ME  04039
1.207.657.5078                                       http://www.maine.com/
Content management, electronic commerce, internet integration, Debian linux