4

I have just been reviewing a vendors database schema when I can upon a table that has a composite primary key on four columns, three of which are floating point data types. I am not sure why but this really shocked me.

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

enter image description here

Phil Murray
  • 6,396
  • 9
  • 45
  • 95
  • Could check this links: http://stackoverflow.com/questions/1105260/sanity-check-floats-as-primary-keys http://stackoverflow.com/questions/1318455/using-datetime-float-representation-as-primary-key – Gonzalo.- Sep 03 '12 at 15:57

3 Answers3

2

I'm guessing this models what is known in CAD as "family table"1. In essence, one "logical geometry" can generate many "physical geometries" by just varying dimensions2, instead of adding or removing geometrical features.

As its name suggests, it's a table - columns are dimensions and each row represents a particular combination of values of these dimensions that should be applied on particular "instance" to produce the physical geometry, which looks awfully similar to what you are showing us here.

There is no purpose in generating two geometrically identical instances, so these dimensions form a key3, which is fine as long as all components of the system "understand" that some numbers (e.g. decimal 0.1) cannot be represented exactly in binary floating-point no matter what the precision.

Depending on where your data comes from (and where it is going), you may stick with the current design or consider using decimal instead:

  • For example, if your data comes from (or is consumed by) a CAD API, it is probably represented as a double there already, so there is less friction just storing it as float in the database.
  • If it comes from some UI where user is expected to enter it in decimal numeric system, decimal might be a better choice.

1 "Family table" is a PTC Pro/ENGINEER (aka. Creo) term. The essentially same thing is known as "iParts/iAssemblies" in Autodesk Inventor and "configurations" in SolidWorks.

2 And other aspects of the parametric design such as feature suppression, but let's not get caught up into too much detail right now...

3 And you do need it regardless of whether you'll introduce additional "surrogate" key and make it primary.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

I recommend you to delete this key and replace it with id key auto increment on table. it's recommended to use integer type for your key, good luck

Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51
0

I don't think there is anything particularly "wrong" with a FLOAT key. The proper type should reflect the column values. Here is a relevant SO question.

Community
  • 1
  • 1
BellevueBob
  • 9,498
  • 5
  • 29
  • 56