Question about a frequently changing column as primary key

pts.
Tags:
Data analysis
Database
DB2
Development
E-business
Ecommerce applications
J2EE
Java
Lifecycle development
Oracle
Programming Languages
SQL
Windows
Thank you for taking take to read my question. We have a live J2EE application using Oracle 8i. Now we are doing v2 to add some modules. We found it has a frequently changing column (status_code) as part of the composite primary keys in some major tables. We know it can cause future proformance issues, however, our application is small and may only have a few thousand records in these major tables. Is it necessary to get ride of status_code from the primary keys? How many records in a table will cause performance problems if it has a frequently changing partial primary key? Thank you very much, Ping Chen

Answer Wiki

Thanks. We'll let you know when a new response is added.

There’s not a hard cutoff line. Having a changing element as a part of a key, primary or not, causes work. The only question is, are you better off indexed or not. You can work out the theory for weeks, or you can try the application with and without the index.

Note also that having a changing primary key makes foreign key constraints difficult in the general case. However, either that doesn’t apply to your application, or have already solved the problem.


Sheldon Linker
Linker Systems, Inc.
linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904 from outside North America

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jolora
    Ideally, if the primary key for this table needs to be passed to another table as a foreign key, your best solution is to add a numeric column to the table to be used as the actual primary key, use an Oracle SEQUENCE to generate unique values for it, and make a UNIQUE key out of the columns that currently make it its primary key. This eliminates all the issues presented by the column or columns that frequently change. Use the new numeric column as the foreign key in other tables. This also makes those secondary tables look cleaner, especially when they contain foreign keys to multiple tables. This is called a SURROGATE PRIMARY KEY, and is a typical and very standard way of designing relational databases. The technique was arrived at to resolve just the issue you are dealing with. If you need the values in your current primary key when SELECTing from the secondary table(s), you can join to this table via the new primary key column. And accessing a table via its primary key is an extremely fast way to access it, second only to using the ROWID. Good luck, Jolora
    0 pointsBadges:
    report
  • Pelaez
    In the long run, a surrogate PK will give you less trouble. It can be implemented via a sequence or an explicit key your code maintains. Oracle e-Business Suite itself has surrogate keys all over, it gives the suite much more flexibility.
    0 pointsBadges:
    report
  • FerencMantfeld
    Jolora has hit it nail on the head. the surrogate primary key using sequence-generated numbers is the best way. Ideally, once you assign a PK value, whether single-column or composite PK, the values should NEVER change. What you should probably do is to just create a unique index incorporating the status code if you need the status code as part of the 'key' for performance reasons, and leave a column like STATUS_CODE out of any primary key. Regards: Ferenc
    0 pointsBadges:
    report
  • Worrab
    Just as an aside, it may be worth re-checking the initial analysis. Sometimes (often?) this situation arises when the primary key has been improperly defined. The principle is that the primary key defines exactly the thing-of-interest. Any other data you hold in the table are attributes of the thing-of-interest (TOI). A changing primary key may indicate that the analysis has not properly identified the TOI or that the primary key contains more fields than necessary. HTH
    0 pointsBadges:
    report
  • Paulachen
    Thank you all for the answers and expertise. They are very valuable. Here is a bit background: This J2EE project is partially alive. We just started to do modification and adding modules. I checked the ER-Diagram and the database. There are numbers of issues: frequently changed status_id(two status: Live/Pending) as primary keys in parent table; data redundancies(5 children tables also keep a copy of Live and Pending records as parents do) This J2EE application is working in prod. It may only have a few thousand records in 5 of the major tables in the future. It sounds there is no impact for now. I do not know, in this situation, if it is necessary to make the major database re-design (use surrogate primary keys instead of frequent changed primary keys, create indexes for search columns, create conjunction tables to reduce data redundancies,etc). I do not know how much impact will be in the FUTURE (such as Oracle 9i+) if we keep the original database structure. Thank you very much, Paula Chen
    0 pointsBadges:
    report
  • Olddba
    One other thing you may want to consider. Everytime the primary key changes, Oracle will place a pointer where the table should be according to its primary key. That pointer will point to where the table actually exists. If you change enough primary keys you will create a situation where every read will take you twice as long and twice as many IOs as it should. Whenever a primary key could change for any reason, a surogate key should be used.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following