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-

 

[MakinBacon91]

Sorry If I wasn't clear,

I am combining tables:

Excel 2013

	A	B	C	D

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D0CECE, align: center"]Part Number[/TD]
[TD="bgcolor: #D0CECE, align: center"]Dimension Name[/TD]
[TD="bgcolor: #D0CECE, align: center"]Max[/TD]
[TD="bgcolor: #D0CECE, align: center"]Min[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]PartXX[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]18.25[/TD]
[TD="align: center"]17.75[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]PartXX[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: center"]2.20[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]PartYY[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]2.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]PartYY[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]99.50[/TD]
[TD="align: center"]100.50[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]PartYY[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]17.00[/TD]
[TD="align: center"]18.00[/TD]

</tbody>

Master Dimensions​

And:

Excel 2013

	A	B

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D0CECE, align: center"]Dimension Name[/TD]
[TD="bgcolor: #D0CECE, align: center"]Definition[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Height[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Width[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Depth[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]Radius[/TD]

</tbody>

Dimension Definitions​

Into:


[TABLE="width: 502"]
<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Excel 2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Part Number[/TD]
[TD]Dimension Name[/TD]
[TD]Description[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PartXX[/TD]
[TD]A[/TD]
[TD]Height[/TD]
[TD]18.25[/TD]
[TD]17.75[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PartXX[/TD]
[TD]B[/TD]
[TD]Width[/TD]
[TD]2[/TD]
[TD]2.2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PartYY[/TD]
[TD]A[/TD]
[TD]Length[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PartYY[/TD]
[TD]D[/TD]
[TD]Width[/TD]
[TD]99.5[/TD]
[TD]100.5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PartYY[/TD]
[TD]Z[/TD]
[TD]Radius[/TD]
[TD]17[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Master Dimensions[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am curious. How are you pasting Excel tables with formatting?
As you can see my table lost formatting when I pasted it from Excel 2010.

Thanks,
-Bacon-

 

[RickXL]

OK, gotcha.

Yes, that sounds like a good approach.

To paste in with formatting I use HTMLMaker. The software is available somewhere here. Have a look at the FAQ. That should have details. There are other options as well. HTMLMaker is an Excel Add-In that runs code to convert your selection into html. I used to have a problem with the Copy and Paste part but I added some code and fixed it.

 

[MakinBacon91]

OK, gotcha.

Yes, that sounds like a good approach.

To paste in with formatting I use HTMLMaker. The software is available somewhere here. Have a look at the FAQ. That should have details. There are other options as well. HTMLMaker is an Excel Add-In that runs code to convert your selection into html. I used to have a problem with the Copy and Paste part but I added some code and fixed it.

Oh okay, sounds good. Are those programs you would have to install on your computer?
I am using a work computer, so it can be a pain to get things installed.

Also, one more question came up.

For most of my tables it has been clear what I need as far as a Primary Key, but when you get down to the Dimensional Table, where you 2 identifiers (Serial Number and Dimension Name) and the actual dimension, I do not see a need for a primary key.

Any thoughts on this?

Once again, thank you for all the help Rick!!

 

[RickXL]

All tables will have a key. That key will uniquely identify the row and everything on that row will depend only on that key.

A key need not be a simple key, though. Compound keys are also allowed. That is, a key may comprise more than one column. In that case, you can imagine the concatenation of those columns being the key.

Both you Dimensions and Master Dimensions tables will have compound keys.


Add-Ins don't need installing like regular programs. They are really just workbooks. So if you can copy a workbook to your PC then you should be good to go. Because it is an Add-In you can choose whether it should be added in or not. You can select it only when you need it. I tend to leave that one there permanently, though.

 

[MakinBacon91]

All tables will have a key. That key will uniquely identify the row and everything on that row will depend only on that key.

A key need not be a simple key, though. Compound keys are also allowed. That is, a key may comprise more than one column. In that case, you can imagine the concatenation of those columns being the key.

Both you Dimensions and Master Dimensions tables will have compound keys.


Add-Ins don't need installing like regular programs. They are really just workbooks. So if you can copy a workbook to your PC then you should be good to go. Because it is an Add-In you can choose whether it should be added in or not. You can select it only when you need it. I tend to leave that one there permanently, though.

Alright, I will definitely look for that add-in.

As far as primary keys,
I have dealt with compound primary keys in the past by making an (Autonumber) ID field, and then using the Indexes, to make a compound unique index.
For some reason or another I was steered away from using compound or composite primary keys. Maybe it takes longer to do a query based on a composite key as opposed to a unique integer key?

 

[RickXL]

I always treat database design as a two stage process.

1. The Logical Design.
That is when you follow the relational rules and get your tables into at least 3rd Normal Form. From that design you should be able to answer the check questions that I posed previously. I think that is the position we are in now. We have something which we think works and the tables all point to list of "real" things with unique identifiers.

2. The Physical Design.
After you have an idealised set of tables that work you can start to simplify or adjust them to get an optimum solution. The database we have been considering only has a few tables. If you are designing an ERP system, say, for a large company then you could end up with more tables than that just trying to record an employee's name and address.

I believe that Ted Codd, in his original work, did not allow missing values in his design rules. He would enforce the use of two tables with a joint key and the tables would contain different numbers of records. Eventually, he concluded that having tables with empty fields does not cause the world to fall apart and can simplify the programming aspects of the job.

The same types of rules apply to compound keys. If you look at the programs you need and find that a compound key will make the coding a lot more difficult then by all means add a single unique key and use that instead. This makes more sense as the number of columns in your key increases.

So I would start to think about the programs you will need and if you can simplify them by using a single, artificial, key then create one.