Re: Auto parts database
At 11:05 PM -0700 4/1/01, Karl Simanonok wrote: Hi Dick, Thanks for your advice. That's a very pretty site at stbtrucks.com (excellent job on the graphics), but coding the back end in Perl must have been a major challenge, I would think. You must have used text files to store the data, instead of using a database, right? I did use text files... this particular Perl subset had no database, nor did it support lists or hashes (structures) I'm trying to get a handle on how to best design a relational database to handle any kind of car part or accessory, have you got any suggestions about that? If you could describe or even just list the columns in your data files (which for Perl is really just a way to manage data in a pseudo-database, right?) that might help a lot. I need to be careful here, because the system is proprietary. You would probably have main tables for: Manufacturer Category/Subcategory Make/Model/Submodel/Year/Subyear Manufacturer Product * Manufacturer Product Options * Application ** * Product and product options get a little tricky the options can determine or affect the product code and the price, e.g.: nnn-XX-YYY-nnn where XX denotes the finish and YYY denotes the size, etc. Now consider that each finish/size combination may have a different price, and, yet a third option may add to that price (stake pocket cutouts in bed rail caps) ** Application (where used) is the key table it denotes where a specific product can be used (which Make/Model/Submodel/Year/Subyear of vehicle). I think we averaged about 3.5 applications per product. I don't know if having a separate color table makes sense... here is no standard usage among mfgrs one calls it teal, another calls it blue or green... Also, you may need to consider a denormalized database to efficiently handle some of the more unusual data relationships. A parting thought... Each year, many (but not all) of the applications will need to be updated to include the new model year. Another... Just building the Make/Model/Submodel/Year/Subyear table is a major accomplishment. HTH Dick I'm thinking that Bud Schneehagen's suggestion of a flag field (or several) in the Products table that indicates what other (out of who knows how many there might ultimately be) tables to look in for supplementary data is probably what I'll wind up having to do. But that seems like a slightly kludgy way to do things, something tells me there has to be a better way... Regards, Karl S. 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
Re: Auto Parts Database?
At 07:13 PM 4/1/01 -0400, you wrote: Sure. I guess you'd have to. Somewhere you're going to have to specify which light lamp fits which vehicle. Better than entering a zillion different lamps. It looks like it's definitely going to need to run on SQL Server since even if you only have 1,000 parts, you're probably going to end up with 100,000 entries in the Availability table. Especially if you get into selling stuff like lamps and wiper blades, etc. We do it roughly this way: Tables: Make (ford, chevy, etc) Model (mustang, corvette) Year Vehicle Attributes (body type, engine size, transmission type, color, etc.) one record for each attribute about the vehicle that needs to be described. This will be different for each vehicle. Primary key for this table is makeid, modelid, year, vehicleattributeid Vehicle Data (each record has two fields, the vehicleattributeid and the data for that attribute) Product (sku, price, manufacturer, etc) Product Attributes (description, shape, color, etc) one record for each attribute about the product that needs to be described. This will be different for each vehicle. Primary key for this table is skuid, , productattributeid Product Data (headlight, square, white, etc) (each record has two fields, the productattributeid and the data for that attribute) Vehicle/Product relational table (many-to-many relationship) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Auto Parts Database?
Karl, I have developed two automotive parts sites at http://www.manhonda.com/store/honda/ and http://www.manhonda.com/store/sports/ which use the schema of their print catalog: accessory type---accessory sub category --- accessory then product description and a table for make, model, year ...etc. It is not exact and many times there is additional interaction required for the customer to get the correct part from the company. To be exact on the web would be very expansive because in many cases the manufacture date, the accessories and the options installed in the car is required to get the EXACT replacement part. Especially since a car made in Jan may use a type-a widget and a car made in Nov uses a type-g widget except when made with accessory B where it then needs a type-g2 widget unless it was manufactured from the west coast plant...smile you get the point. There is no discernable standard that I have seen and it is a trade off of functionality, end user knowledge, and design and maintenance costs. Best Regards, Dennis Powers UXB Internet (203) 879-2844 http://www.uxbinfo.com/ -Original Message- From: Karl Simanonok [mailto:[EMAIL PROTECTED]] Sent: Saturday, March 31, 2001 6:40 PM To: CF-Talk Subject: Auto Parts Database? 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... Regards, Karl Simanonok Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Auto parts database
I am working on a truck accessory web site too and would like any other info out there on building the most efficient database for this type of product line. I've managed to get the site up and running with a limited number of products but I know there must be a better way. I won't put the URL but if you'd like more info on how I pieced it together this far - or if you have valuable info for me - just shoot me an email. Donna French Work: [EMAIL PROTECTED] Home: [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Auto Parts Database?
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
Re: Auto Parts Database?
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 pric
Re: Auto Parts Database?
On 4/1/01, Karl Simanonok penned: 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? Sure. I guess you'd have to. Somewhere you're going to have to specify which light lamp fits which vehicle. Better than entering a zillion different lamps. It looks like it's definitely going to need to run on SQL Server since even if you only have 1,000 parts, you're probably going to end up with 100,000 entries in the Availability table. Especially if you get into selling stuff like lamps and wiper blades, etc. 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 have a color table in the database with Color IDs and Color Names. The products table has a field which is just a list of color IDs. If that's not empty then it runs a query of the list items to pick up the colors from the color table. I guess you could go ahead and enter every color of every vehicle ever made if you wanted. LOL http://www.cf-ezcart.com/ Click on Glass Creations and scroll down to the peacock. 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. Okeydoke. I'm pretty much stuck on using my own cart, which may not be a solution if you're going to be doing a site for Discount Auto Parts or someone that carries every part for every vehicle ever made. Hell, even if you could build it in a week, it would take 2 years to enter all the parts. LOL -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Auto Parts Database?
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... Regards, Karl Simanonok Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
