Simplify primary key

[imron95]

Hi gurus...

I'm designing primary-key for a table, and there have to
be 17 fields included for the primari-key, let say F1,F2...F17
I'm consedering about its performance during DML and
data retrieval.

is there any way to shrink the number of key-fields into only 1 field
may be by doing certain calculation towards those 17 fields, so
the calculation result will be "guaranteed" unique.
I remember about Hash Function, could it help? I have no idea.

Thanks in advance...

[marist89]

I think the preferred method would be to use an "ID" field and populate it with a sequence & trigger. To guarantee F1-F17's uniqueness, you could create a Unique constraint on those fields.

[jrpm]

The quick and easy answer is to use a sequence to assign a unique primary key.

To be honest, I have to question a design that uses 17 fields for the primary key. I suspect the table can be broken up into two or three, depending on the application query patterns, that would be more manageable at the physical level. If someone really insists on the 17 field key structure for a monolithic table, give them a view in that structure.

[imron95]

I can't do that way, since the system is interfacing with
other several systems accross machine,city, even country,
and they cannot accept the sequence number generated from
the sequence. Has no possibility to ask them to change
their system.

When certain system needs information of particular data
it simply send us the 17 keys, so that my app need to search
by 17 fields, If I have a computed field I can easily
calculate tose field and search through the table by only 1 key-fields.
that's my idea, does oracle provide such function????

[chrisrlong]

I have to agree with the others that 17 is way beyond ridiculous. This is a major red-flag that likely denotes a very poor design.

However, that doesn't help you in your current plight. If you need 17 fields, then you need 17 fields. Although I would also agree with the other post-ers that those 17 should be made an AK and a new PK should be created using a surrogate key filled via a sequence. At least you can stop the insanity from spreading any further.

So how large are these fields? If they are small/medium numerics, I wouldn't worry about the DML. However, if they are large numerics/strings, then you may have an issue. However, what would be the solution? What you are asking for is some way to hash all these large values into a single, smaller value. While that is possible, what would you have lost? You would have lost the ability to find the individual pieces. Say you created a function to convert these 17 fields into a nice, medium-sized number. You then store this number in the table as the PK/AK. So, when somebody asks you for the info on something with all 17 fields, you process those values with the function, then use the resulting value in a search trhough the table - record found - no problem. But what if they want more than one record and therefore send you less than 17 fields? Then you're screwed. You have changed the atomic level of the data from any 1 (or more) of the 17 fields to no less than all 17 fields!! So, this has major fundamental drawbacks that are most likely not worth exploring further.

Another possibility? Make a conversion table instead of a function. So now you have a table with 18 columns - one surrogate PK filled via a sequence and 17 fields in the AK. Now you can use a subset of the 17 fields to get to multiple rows. But wait - doesn't this look an awful lot like the table we first proposed? Why yes it does . Actually, there are no real advantages to creating this conversion table as opposed to simply using this as the basis for the actual table.

So, as the other post-ers have already stated, make the 17 fields the AK and add a new, sequence-filled PK. (and commence with weekly tirades about the 17 fields until they decide to fix things on their end as well).

I don't understand why you think this will affect how you interface with other systems. You simply use the new PK for any child tables and processing on your side and use the 17 fields for interfacing with their side. There is no restriction on functionality. You are simply stopping the insanity of 17 fields from propogating further.


HTH,

- Chris

[ligang]

agree with chrisrlong

[chrisrlong]

Originally posted by ligang
agree with chrisrlong

Hey - there's a first!

[gandolf989]

I also agree with ChrisRLong. Stop the madness. There are probably a few large CHAR's mixed in those 17 fields somewhere.