Friday, March 23, 2012

help creating complicated database...

Hi,

for my website im going to be selling rims, and i need to have available a search by vehicle

SKU: 8438743
CAT: RIM
BRAND: ADVAN
MODEL: KREUZER_SERIES_V
DIAMETER: 17
WIDTH: 8
OFFSET: 45
LUGS: 5
LUG SPACING: 114.3
FINISH: GMETAL

that would be the determing factors of the results, heres what would define for example a

2002 Accord V6

LUGS = 5
LUG SPACING = 114.3
55 > OFFSET > 40
DIAMETER < 20
WIDTH < 9

so for the accord
the required attributes are a 5X114.3 lug pattern
the offset needs to be 40-55
max diameter is 19"
max width = 8"

My question is this, how many databases do i need to make? and what catagories do I use in each database? Also is it possible to do less than, greater than, etc, in databases? I think thats everything, thanks!I think you need two tables.

table Rim
RimID
Sku
Cat
Brand
Model
Diameter
width
Offset
lugs
lugspacing
finish

table CarRimRequirements
CarID
CarModel
Lugs
Lugspacing
MaxDiameter
MaxWidth
OffsetMin
OffsetMax

You can't really put a range in one column, use a minumum and maximum column like I did for the offset.

Hope that helps!
KJ|||This is just another suggestion.

tblRim

RimID
RimDescription
SKU
Brand
Model
Finish

tblAttribute

AttributeID
AttributeName

tblRimAttributes

RimAttributeID
RimID
AttributeID
Value

tblCar

CarID
CarModel

tblCarAttributes

CarAttributeID
CarID
AttributeID
Requirement – Allow this field to have null.

This is how it relates to your example.

tblRim

RimID RimDescription SKU Brand Model Finish
1Rim 8438743 ADVAN KREUZER GMETAL

tblAttribute

AttributeID AttributeName
1Diameter
2Width
3Offset
4MaxOffset
5MinOffset
6Lugs
7LugSpacing

tblRimAttributes

RimAttributeID RimID AttributeID Value
1 1 1 17
2 1 2 8
3 1 3 45
4 1 6 5
5 1 7 114.3

tblCar

CarID CarModel
1 2002 Accord V6

tblCarAttributes

CarAttributeID CarID AttributeID Requirment
1 1 1 19
2 1 2 8
3 1 4 55
4 1 5 40
5 1 6 5
6 1 7 114.3

If you are using Stored Procedures first you could get all the requirements for the selected car.

Then you could pass these values as parameters to another Stored Procedure, which would return all the Rims which meet these requirements.

No comments:

Post a Comment