Re: Truck Dealer Database

jaymcgraw_at_gmail.com wrote:
> I am building a database to track and display (web, intranet) a truck
> dealer's vehicle inventory. I'm running into difficulties in designing
> the structure of the tables to hold the truck information.
>
> Brief Description:
> There are different types of products that they sell: Buses, Heavy
> Trucks, Medium Duty Trucks, Light Trucks, even Trailers.
> Each individual vehicle/unit has a Dealer-assigned Stock # (never a
> duplicate).
> Each individual vehicle/unit is either New or Used.
> Each individual vehicle/unit has a Factory-assigned Serial #.
> Eavh individual vehicle/unit has a number of attributes, ie: year,
> make, model, engine, transmission, wheelbase, gvw, and on and on and
> ON.
>
> Difficulty #1: Different product units have different attribute types.
> --- The different units carry different attributes important to someone
> wanting to know about that type of unit. For instance, a light truck
> has what is called a trim. A heavy truck does not have that. A heavy
> truck has a cab-to-axle measurement that a trailer does not have. And
> so on.
>
> Difficulty #2: Trucks are HIGHLY customizable. --- Although there are
> duplications of the same vehicle where all of the attributes are
> exactly the same, it's somewhat rare that the dealer has those in
> inventory. They may have 20 2005 Ford F150s, but stock # 12345 might be
> different in color, while stock # 12346 is different in color and has a
> different engine spec.
>
> Any suggestions as to how to setup the table(s) for this? I could have
> one table with the Stock # as the Primary Key, but I would have
> multiple attribute fields as either NULL or redundant. For instance:
>
> Stock# Serial# Type Year Make Model Color Fuel Trim Tran
> 12345 A53234 LT 2006 Ford F150 Green Gas XL Auto
> 12346 A83493 LT 2006 Ford F150 Green Dies XLT Auto
> 12347 A53d34 LT 2006 Ford F150 Blue Gas XL Auto
> 12348 A8da93 LT 2006 Ford F150 Green Gas XL Man
> 12349 A8d092 HT 2003 Ster AT75 White Dies Man
> 12350 Ad3921 HT 2004 Ster AT75 White Dies Man
> 12351 A499d3 HT 2004 STER AT85 White Dies Man
> 12352 TR J-CR PUP Blue
> 12353 dfdfdd TR J-CR PUP Green
>
> Keep in mind the number of attributes is far beyond that shown above.
>
> And I could separate the attributes into separate tables depending on
> the product type, but then I'm stuck with ugly one-to-one
> relationships. I'm not sure where to go from here. Any help would be
> greatly appreciated!!!
>

One would really need to know about how the database is going to be used before recommending table structure, but ...

Consider a star schema. One main table, with Stock# as the primary key. A number of fact tables, with Stock# as a foreign key. No nulls stored. For example:

Non-null attributes in main table:

Stock# Type Make Model Color
------ -- ---- ---- -----
12345 LT Ford F150 Green
12346 LT Ford F150 Green
12347 LT Ford F150 Blue
12348 LT Ford F150 Green
12349 HT Ster AT75 White
12350 HT Ster AT75 White
12351 HT STER AT85 White
12352 TR J-CR PUP Blue
12353 TR J-CR PUP Green

Serial# table:

Stock# Serial#
----- ------
12345 A53234
12346 A83493
12347 A53d34
12348 A8da93
12349 A8d092
12350 Ad3921
12351 A499d3
12353 dfdfdd

Model-year table:

Stock# Year
----- ----
12345 2006
12346 2006
12347 2006
12348 2006
12349 2003
12350 2004
12351 2004

N. Shamsundar
University of Houston Received on Fri Oct 28 2005 - 17:52:41 CEST