It is even worse than you have described...
Way back in '98 i did a custom shopping cart for this in Perl subset
and no database.
here is a brief description of the site from my resume:
This is a specialized shopping cart that was custom-written to address a
specific set of issues related to the auto accessory industry.
The product line is complex, including issues not addressed by most shopping
carts:
-Many different manufacturers, each with their own SKU numbering scheme
-Up to 4 options per product
-The Manufacturer's SKU may be determined by the product options selected
-Some options determine price, others increase the price, still others have
no affect on price
-Many products have several applications (a bug deflector may fit several
different make/model/year combinations of vehicles). This means that the
shopping cart must contain both product information and application
information
-Many products include detailed descriptions, dimensions, vehicle submodel
designations, installation requirements, etc.... the shopping cart must
contain these for each product, when present
The shopping cart and the search routines were written in a Perl subset. This
subset has no sort. loop, subroutine or database functions. These functions
were implemented as part of the application programs.
The cart and search were integrated into the overall site design
(graphics and
layout) by Henry Perlmutter.
You can see the cart at:
http://www.stbtrucks.com/www4/
Browse some of the product categories, especially:
Rocker Panels
Bed Protection---> Bed Caps---> Bed Rail Caps
There is absolutely no standardization within the industry... you
need to create the db from the mfgr. product spec sheets which often
contain confusing notes, etc.
HTH
Dick
At 2:06 PM -0700 4/1/01, Karl Simanonok wrote:
>Hi Bud,
>
>Thanks for your thoughtful reply. I follow your logic, which makes sense,
>but it doesn't cover all cases. For example, let's say you've got light
>bulbs in your database like 1152's and 1157's (I think those are valid
>numbers). Do you have an entry in the Availability table for every single
>vehicle they're used in?
>
>What do you do if you have say, fender skirts in three different colors,
>all costing the same? Do you represent these with three different entries
>in the Products table? If so you need a Color field. But colors won't
>apply to all products so in that case you've got a bad DB design. You
>could have a separate Colors table with just one record in the Products
>table for all three colors but then you need some kind of a flag in the
>Products table which tells you to look there, and logic requiring at least
>two queries to get all the information you need. How do you see resolving
>this problem?
>
>I'm not in a major rush but it's coming up soon. I will happily share with
>you any solutions discovered or invented. Thanks for your help, Bud.
>
>Regards,
>
>Karl Simanonok
>
>At 07:03 AM 4/1/2001 -0400, you wrote:
>>On 3/31/01, Karl Simanonok penned:
>>>Can anyone on the list point me toward an existing auto parts database
>>>schema? Because of the many vehicles they can fit on, different ways that
>>>manufacturers refer to them, and the different ways individual parts can be
>>>categorized it seems that a database to accommodate them all must be quite
>>>complex, perhaps with different tables for different types of
>>>parts. Rather than reinvent the wheel I'm hoping someone can help me out
>>>here...
>>
>>Karl. Let me know what you come up with. I'm in the same boat myself. I'm
>>going to be building a store for truck/jeep accessories. I've already got
>>my shopping cart which breaks down into Manufacturer, Main Category,
>>Category, Product. I've got a Styles table that I can relate miscellaneous
>>features of a part to, and it is capable of changing price based on size
>>and can offer different colors, which of course, dealing with autos
> >probably won't work since you can't really just say "Red". What I'm
>>planning on doing is this.
>>
>>I'm going to add 3 tables to the database:
>>Make ... related to;
>>Model ... related to;
>>Availability (or something to that extent)
>>
>>Make will consist of: Make_Name, Make_ID.
>>Model will consist of: Make_ID, Model_Name, Model_ID.
>>Availability will consist of:
>>Model_ID, Product_ID, Year_Min, Year_Max, Part_Price
>>
>>So, for instance I'm going to put an "Interceptor Bug Shield" as a Product
>>in the database with an ID of 001. I only have to put it once and only
>>have to make one picture. Then say for argument sake, the product is
>>available in 40 different makes and models of vehicle. I'll have to put 40
>>records in the availability database. If the product is available for a
>>Ford Ranger (Model_ID 100) going back to the year 1980 thru now and sells
>>for a price of $50.99, that record will look like:
>>
>>Model_ID, Product_ID, Year_Min, Year_Max, Part_Price
>>100,001,1980,2001,50.99
>>
>>Suppose that the price for the bug shield cost more for the Ranger if the
>>year is between 1980 and 1989, say $60.99.
>>
>>
>>100,001,1980,1989,60.99
>>100,001,1990,2001,50.99
>>
>>I'm thinking about probably making Year_Max nullable, so basically you
>>won't have to go in every year and change the 2001 to 2002 for a zillion
>>records. Basically it would just consider the year as now() if that field
>>is null. So for the scenario above, the records for the Ranger's product
>>would look like:
>>
>>100,001,1980,1989,60.99
>>100,001,1990,null,50.99
>>
>>Then we could have a query so that if for some reason Ford pulls the plug
>>on the Ranger and makes 2002 the last year, before the end of 2002 we do:
>>
>>UPDATE Availability
>>SET Year_Max = 2002
>>WHERE Model_ID is 100 and Year_Max is NULL
>>
>>It's going to be a week or so before I get into this full bore, so if
>>you're not in a do or die situation...
>>--
>>
>>Bud Schneehagen - Tropical Web Creations
>>
>>_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
>>ColdFusion Solutions / eCommerce Development
>>[EMAIL PROTECTED]
>>http://www.twcreations.com/
>>954.721.3452
>
>
>Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
>Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
