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!

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

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!

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

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?

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'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

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

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

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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.