SQL Server Component Key Four Columns of Different Data Types

Question

I just looked at the provider database schema when I can, on a table with a composite primary key in four columns, three of which are floating point data types. I don’t know why, but it really shocked me.

What are the implications / benefits of having such a composite primary key? Is this recommended? I thought floats were dangerous as primary key fields.

Answer 1

I assume that these models are known in CAD as a "family table" ¹ . In fact, one “logical geometry” can generate many “physical geometries” using only variable sizes ² instead of adding or removing geometric features.

As its name implies, a table column is a dimension, and each row represents a specific combination of the values ​​of these dimensions that must be applied to a particular “instance” to create physical geometry that looks terribly similar to what you are showing us here.

There is no purpose in creating two geometrically identical instances, therefore these measurements form a key ³ which is good so long as all components of the system “understand” that some numbers (for example, decimal 0.1) cannot be accurately represented in binary floating point, regardless of accuracy .

Depending on where your data comes from (and where it happens), you can stick with an existing project or use decimal instead

• For example, if your data comes from the CAD API (or is consumed), it is probably represented as a double there, so there is less friction, just saving it as a float in the database.
• If it comes from some interface in which the user must enter it in a decimal numeric system, decimal may be a better choice.

---

¹ "Family table" is the term PTC Pro / ENGINEER (aka. Creo). Essentially, this is the same as iParts / iAssemblies in Autodesk Inventor and configurations in SolidWorks.

² And other aspects of parametric design, such as feature suppression, but don't let us get into too much details right now ...

³ And you need it, regardless of whether you enter an additional "surrogate" key and make it primary.

Answer 2

I recommend that you delete this key and replace it with an automatic increment of the key key in the table. It is recommended to use an integer type for your key, good luck

Answer 3

I don’t think there is anything particularly “wrong” with the FLOAT key. The correct type should reflect the values ​​of the columns. Here is a relevant SO question.