Table design for engineering valve pricing

[Terri58ds]

I need help with a table design for on a DB involving piping, valves and fittings. The table has 5 fields and uses a combination of 4 fields to identify each valve and insualtion thickness to get the item price. It appears that so many fields are repeated data that I question if it is normalized.


I need the following fields to identify each valve
NPS (Pipe size eg: 1", 2", 3", 4", 6". 8", 10" etc - 16 sizes total)
Rating (Pound rating eg: 150#, 300#, 600#, 900#, 1500#, 2500#. - 6 values total)
Valve type (Gate Valve, Check, Butterfly, Control, Ball - 5 values total)
Insulation thickness (1", 1.5", 2", 3" - 4 values)
Price (variable $ amount)

NPS Rating   Type   Thick   Price
1    150     Gate      1       23.50
2    150     Gate      1       31.25
3    150     Gate      1       49.78 
........
8    150     Ball      1       54.65
10   150     Ball      1       62.32
.......
8    300     Ball      1       78.25
8    300     Ball      1.5     84.65
.......

The first four fields would be a combined PK.

The problem can be compared to buying jeans where there is a unique combination of waist size, inleg length, colour, style/fit to get the right item and then find the price.
In my case each item has a unique price, unlike jeans which have one price for all sizes in a particular style.

Thoughts? Suggestions?

 

[spikepl]

WHat is your question/concern? You can make a composite primary key, or you can add an autonumber, and make a composite index on the 4 fields you mention.

Getting/storing references to values in a table using a key composed of 4 fields is most often major PITA ...

 

[plog]

Its good you are questioning your methodology and looking for the best way to do so. With that said, you are doing it correctly.

The only alternative is to move some of the permutations to another table, but that doesn't really do anything for you. For example, in your sample data you listed 3 unique permutations of ratings and types:

Rating, Type
150, Gate
150, Ball
300, Ball

You could make that a table, assign a unique autonumber ID to each permutation and then replace those 2 fields in the sample data you posted above with that ID:

NPS, RatingTypeID, Thick, Price
1, 1, 1, 23.50
2, 1, 1, 31.25
3, 1, 1, 49.78
8, 2, 1, 54.65
10, 2, 1, 62.32
8, 3, 1, 78.25
8, 3, 1.5, 84.65

You've now rolled all those similar ratings/types permutations into another table, but you only made your data harder to understand. Now if Price was some sort of function of all those 4 things (NPS*.78 + Rating/Thick - iif(Type="Gate", 5, 7)) then you could eliminate the table completely. Other than that, you've done it correcty.

 

[ButtonMoon]

I need help with a table design for on a DB involving piping, valves and fittings. The table has 5 fields and uses a combination of 4 fields to identify each valve and insualtion thickness to get the item price. It appears that so many fields are repeated data that I question if it is normalized.

There seems to be nothing obviously "wrong" with your design. Normalization really has nothing to do with eliminating data that is "repeating" - although it is sometimes wrongly described that way.

Do you need to record a history of changing prices? If so then you might want to add some dates to your table or create a separate history-preserving version of the table.

 

[Terri58ds]

Thanks for the replies and your reassurance folks.

Plog, I had been wondering if I needed to do something like that but I agree, it does just complicate the table (not easily readable) and adds another layer. Nope, price is just a hardcoded number.

Repeating the same word just looked odd and started messing with my mind.

Thanks again.