Tricky Relational Question

[MakinBacon91]

Hello All,

I was trucking along with my database design, but now I have run into a brick wall, and I think I may have to break the relational nature of my database.

I have a table that I am going to fill in with dimensions for different parts in a set.
The fields of this "Dimensional" table are: Serial Number ID, Dim A, Dim B, Dim C, etc...

For each of these dimensions there are limits.
The way I was thinking to set this "Limits" table up is:
Fields: Dim, Maximum, Minimum, Nominal, etc...
With each dimension being a record: Dim A, Dim B, Dim C, etc...

Setting it up this way I can think of no way to link the Dimensional table with the Limits table in a relational nature.
I can obviously use the Limits table and Dimensional table together by writing some VBA code, but this would not allow me to query for my Limits. (At least I don't think)

Any thoughts?

Thanks in advance!
-Bacon-

 

[RickXL]

Hi,

So how do the limits relate to the dimensions?

Are the limits connected with a particular dimension or a particular part or something else?

Note: there may be a case for putting the dimensions in their own table with one row per serial number/dimension.

By the way, you never "have" to break the relational model. All data can be made to fit. Ted Codd proved that before IBM started selling relational databases.

 

[MakinBacon91]

Hi,

So how do the limits relate to the dimensions?

Are the limits connected with a particular dimension or a particular part or something else?

Note: there may be a case for putting the dimensions in their own table with one row per serial number/dimension.

By the way, you never "have" to break the relational model. All data can be made to fit. Ted Codd proved that before IBM started selling relational databases.

There is one set of limits that will be associated each dimension taken.
I.E. We record Dimension A from a part, and Dimension A can be between a Max of 18.25 inches and a Min of 17.75 inches.
Dimension B has different limits, Dim B has a Max of 2.010 and a Min of 2.000 inches.

Right now I was thinking of putting Each part/SN in a row, with all of the dimensions in the fields.
I am just unsure of how to tie in the limits.

Thanks,
-Bacon-

 

[RickXL]

OK, is it like this?

You need a Master Parts table that has details relating to a part. For instance, part number, description, price, min and max dimensions.

Then you need an Items (or Inventory) table that has a list of serial numbers. Each serial number will have list of actual dimensions and also the part number. You can then use part number to find out what the mins and maxes are.

 

[MakinBacon91]

OK, is it like this?

You need a Master Parts table that has details relating to a part. For instance, part number, description, price, min and max dimensions.

Then you need an Items (or Inventory) table that has a list of serial numbers. Each serial number will have list of actual dimensions and also the part number. You can then use part number to find out what the mins and maxes are.

Are you saying structure it like this?:

Master Parts Table:
Part. No | Description | Max Dim A | Min Dim A | Max Dim B | Min Dim B | Max Dim C | Min Dim C
partXX | blade | 18.25 | 17.75 | 2.010 | 2.000 | 1.875 | 1.700
partYY | blade | etc...

Inventory Table:
Serial No.| Part No. | Dim A | Dim B | Dim C
ABC123 | partXX | 18.00 | 2.000 | 1.750
ACB939 | part YY | etc...


If so, I don't think that will work for my case, because partXX could have dim A,B, and C, but partYY could have dim A-Z.

I think that you may have led me to a solution though.

Master Dimensions Table:
DimID | Part No. | Dim Name | Max | Min
(Auto Number) | part XX | Dim A | 18.25 | 17.75
(Auto Number) | part XX | Dim B | 2.010 | 2.000
(Auto Number) | part yy | Dim A | etc...
(Auto Number) | part yy | Dim B | etc...
(Auto Number) | part yy | Dim C | etc...
etc...

Dimensions Table:
DimID | Serial No. | Dim
(Auto Number) | ABC123 | 18.00
(Auto Number) | ABC123 | 2.000
(Auto Number) | ABC939 | 1.75
(Auto Number) | ABC939 | 18.00
etc...

The (Auto Numbers) will be a unique Identifier between the two tables.

What do you think?

 

[RickXL]

If I am understanding this correctly the data looks like this ...

You have some Parts which are uniquely identified by part numbers:

Excel 2013
	A	B	C
1	Part Number	Description	Price
2	PartXX	Top Flange	£20.00
3	PartYY	Lower Widget	£125.00
Master Parts

Then you have some master dimensional limits which are unique to a part number and a dimension name:

Excel 2013
	A	B	C	D
1	Part Number	Dimension Name	Max	Min
2	PartXX	A	18.25	17.75
3	PartXX	B	2.00	2.20
4	PartYY	A	1.00	2.00
5	PartYY	D	99.50	100.50
6	PartYY	Z	17.00	18.00
Master Dimensions

Then you have some actual dimensions for each serial number and dimension name:

Excel 2013
	A	B	C
1	Serial Number	Dimension Name	Actual
2	ABC123	A	17.75
3	ABC123	B	2.20
4	ABC124	A	2.00
5	ABC124	D	100.50
6	ABC124	Z	18.00
Dimensions

And finally you need to say which serial numbers are which part:

Excel 2013
	A	B
1	Serial Number	Part Number
2	ABC123	PartXX
3	ABC124	PartYY
4	ABC125	PartXX
5	ABC126	PartYY
6	ABC127	PartXX
7	ABC128	PartYY
8	ABC129	PartXX
9	ABC130	PartYY
Parts

Finally, you need to define the dimensions somewhere:

Excel 2013
	A	B
1	Dimension Name	Definition
2	A	Height
3	B	Width
4	D	Depth
5	Z	Radius
Dimension Definitions

The two tables with Master in the title will need to bne updated when a new type of part is defined.
The last two tables will need to be updated each time a new instance of a component is manufactured and measured.

 

[RickXL]

I don't know if the dimension names are unique in themselves or depend on part numbers. I assumed unique.

When you get a structure you are happy with you need to do some checks.

Is all the data in there?
Can I join it all together properly and unambiguously?
When I enter the data, will all the necessary tables for checking exist?
Will the data exist in the right order? That is, will I ever need something that is not due to be entered yet?
Will the reports you require be available?
Am I sure that I am not entering the same data twice anywhere?
Am I sure that I will never need to add a column to a table for any updates?

This list is not exhaustive.

 

[MakinBacon91]

That is exactly what I was going for.
The only change I made was combining your Master Dimensions and Dimension Definitions table into one table.

Edit: I did this because you were correct in assuming the dimension names are uniquely dependent on the part numbers.

 

[MakinBacon91]

Thanks for all the responses Rick!

From your checklist I think I have all my requirements met, the only one I will have to figure out is the third point. "Will the data exist in the right order? That is, will I ever need something that is not due to be entered yet?"
Which I guess means that I will need to have all of my Master Dimensions, Master Parts, and Parts tables set up before I try to input any dimensions.

 

[RickXL]

The way I understand it, Dimensions and Master Dimensions need to be separate.

Master Dimensions is keyed on Part Number and Dimension Name while Dimensions is keyed on Serial Number and Dimension Name.

I am assuming that a Part Number will be for something like a Widget. Let's say PartAA is a Widget. Now if I make one then that will have a serial number of 000001. If I make another it will have a serial number of 000002, the third one will be 000003 etc. All the Part Numbers will be PartAA, though.

My assumption is that the Max and Min dimensions apply to all Serial Numbers for a Part. Only you know if that is true or not.

If that is not true and there is a different Max and Min for each Serial Number then max and min are just extra columns on the Dimensions table.