Newbie Help

[mossman65]

Hello Group!

Its been about 5 years since i have messed with SQL.

I'm creating a database for a simple .xls that will be searchable.

Here is a exmple row of data:

Make:ACURA
Model:INTEGRA
Year:1990
Engine: L4
Edition: GS
Trans Cooler: x=true
Oil Cooler:
Horse Power:0-400
Comments:
Part Number:9E-HE790-01
Tube Size:1.50"
Rows:1
Core Size:13.00 x 26.88

I have broken into the following tables: {}=colums in the table

Year:
{vehicle_year}
Make:
{vehicle_make}
Model:
{vehicle_model}
Vehicle_specs
{cooler_type}
{engine_size}
{horsepower}
{comments}
{tube_size}
{rows}
{core_size}

Product_id
{part_number}
{image_path}
{image_name}

Does this seem to be a solid way to design the tables? Also Some things have Changed since SQL2k... I have all but the Primary Key set to a (char) Data Type.

All Comments and criticism are welcomed!

[dickersonka]

do you have id columns? like vehicle_make_id and vehicle_make?

also model needs to be fk's to make, spec fk'd to model, part fk'd to model i would say, and you prob want a product_category_id

we'll talk about the char after that

[mossman65]

do you have id columns? like vehicle_make_id and vehicle_make?

also model needs to be fk's to make, spec fk'd to model, part fk'd to model i would say, and you prob want a product_category_id

we'll talk about the char after that

Thank you for your help!!! Here is what i have now:

Product Table:
product_id (set as PK)
part_num
image_name
image_path

Vehicle Year
year (set as PK)

Vehicle Make:
vehicle_make_id (set as PK)
vehicle_make

Vehicle Model
vehicle_model_id (set as PK)
vehicle_model

Vehicle Specs These will be displayed after search has completed
product_id (set as PK)
engine
edition
trans_cooler
oil_cooler
horsepower
comments
tube_size
rows
core_size

Am i making this more difficult than it needs to be? Could i have one table with all the data:

ACURA
INTEGRA
1990
L4
GS
0-400
9E-HE790-01
1.50" 1
13.00 x 26.88

SELECT * FROM Products WHERE vehicle_make = Acura, vehicle_model = Integra, year = 1990

the only thing is there are 100,000 rows for it to run against.

Thanks!

[dickersonka]

yes you could, but i would normally stick with a normalized structure like this

you need the foreign keys, a model is tied to a make

vehiclemodel
VEHICLE_MODEL_ID
MAKE_ID
VEHICLE_MODEL

do you understand what i mean that way?

that allows a model to correspond to a make, and spec correspond to model, and product to model

[mossman65]

yes you could, but i would normally stick with a normalized structure like this

you need the foreign keys, a model is tied to a make

vehiclemodel
VEHICLE_MODEL_ID
MAKE_ID
VEHICLE_MODEL

do you understand what i mean that way?

that allows a model to correspond to a make, and spec correspond to model, and product to model

I believe i understand...
when the end user selects a model and a make, it will run against the main product table. In the main product table there will be a vehicle_model_id and a make_id that will allow the search pull back results based on those two variables?

The one thing that i haven't mentioned is that the client will be updating the database via a excel file, so i guess i will need a main table to maintain the structure of the excel file in some manner so the update statement will work?

[dickersonka]

i'm not sure how vehicles parts necessarily work, but if they "share parts" ie one part works in multiple models then yes you need a model_id and make_id, if they don't share parts, you need only a model_id, and also remember you will need a year_id because i doubt all parts will work across years

do you have a sample row of the excel file? that may give a more clear understanding to the relationship

[mossman65]

i'm not sure how vehicles parts necessarily work, but if they "share parts" ie one part works in multiple models then yes you need a model_id and make_id, if they don't share parts, you need only a model_id, and also remember you will need a year_id because i doubt all parts will work across years

do you have a sample row of the excel file? that may give a more clear understanding to the relationship

I have attached a PDF

I guess i should have given a better breakdown when i posted this thread :)

The company makes custom radiators for old to new to NASCAR vehicles.

The search function allows the person restoring/modifying/building a car to find the radiators that would fit the year, make, model. The only items within the excel file they want searchable is the year/make/model.

At that point the client wants them to call the order in (tried to get them to use a cart) to double check that the end user knows what they are getting and that it will fit the vehicle properly. I guess in many cases in the past they have had to ship back the radiator due to the end user not knowing exactly what they needed.

Kind Regards,

Aaron

[dickersonka]

ahhh, see a lot of the same part available to multiple editions of the same model and of different years

i would suggest creating a part2models table

part2models
PART_TO_MODEL_ID
PART_ID
MODEL_ID
EDITION_ID

you can use your parts table you had

make sure you add a year_id to the models table, and edition table for the model

edition
EDITION_ID
MODEL_ID
EDITION_NAME

[mossman65]

ahhh, see a lot of the same part available to multiple editions of the same model and of different years

i would suggest creating a part2models table

part2models
PART_TO_MODEL_ID
PART_ID
MODEL_ID
EDITION_ID

you can use your parts table you had

make sure you add a year_id to the models table, and edition table for the model

edition
EDITION_ID
MODEL_ID
EDITION_NAME

So would i have a Model table:
model_id
vehicle_make
vehicle_year
vehicle_model

For the main search, once that search is preformed it will check against the part2model table for matching part_2_model id's where model_id is equal to the part_2model id.

part2models
PART_TO_MODEL_ID
PART_ID
MODEL_ID
EDITION_ID

And finally the parts table that would hold a part2model_id that would bring back all results within that table that matched the part_2_model id?

part_2_model_id
part_id

[dickersonka]

For the main search, once that search is preformed it will check against the part2model table for matching part_2_model id's where model_id is equal to the part_2model id.

no it will check against the model id's, you have the model_id
part_to_model_id is just a key for this table, you don't necessarily need it, i think its just best to have a pk for each table

And finally the parts table that would hold a part2model_id that would bring back all results within that table that matched the part_2_model id?

parts table will hold a part id

the parts2model table related what parts, belong to which mode/edition

you will query against part2models, you will know the model (model_id) and part (part_id) and possibly edition (edition_id)