Simplify primary key
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Simplify primary key

  1. #1
    Join Date
    May 2002
    Posts
    22

    Cool Simplify primary key

    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...


    let's eat fish!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  4. #4
    Join Date
    May 2002
    Posts
    22
    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????
    let's eat fish!

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Last edited by chrisrlong; 10-29-2002 at 10:00 AM.
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    agree with chrisrlong
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by ligang
    agree with chrisrlong
    Hey - there's a first!
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    I also agree with ChrisRLong. Stop the madness. There are probably a few large CHAR's mixed in those 17 fields somewhere.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width