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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
