aspose file tools*
The moose likes JDBC and the fly likes Only one short code in DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Only one short code in DB" Watch "Only one short code in DB" New topic
Author

Only one short code in DB

maganti suryanarayana
Ranch Hand

Joined: Mar 30, 2010
Posts: 53

Hi experts,

I had a ticket request with me and i am not knowing how to handle. Let me explain the whole scenario in a clear manner.
In my database i have the primary key as firstCode which is six digit long ex: PQRS11 and shortCode(Which is not a primary) which is three digit length:PQR

This firstCode i can create for various organizations, such as PQRS11, PQRS12,PQRS13. The shortCode i generated in java using substring(0,3) which gives me PQR.

The problem is that in DB only one PQR should exist. If i select PQRS11 and select a radio button to generate shortCode it should check in the DB and if any PQR exists
then it should delete that and update the same for PQRS11.

How to do this ? I am in great confusion

Thanks in advance

Surya


surya
Maneesh Godbole
Saloon Keeper

Joined: Jul 26, 2007
Posts: 9993
    
    7

Moving to a more suitable forum...


[How to ask questions] [Donate a pint, save a life!] [Onff-turn it on!]
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

maganti suryanarayana wrote:
In my database i have the primary key as firstCode which is six digit long ex: PQRS11 and shortCode(Which is not a primary) which is three digit length:PQR
This firstCode i can create for various organizations, such as PQRS11, PQRS12,PQRS13. The shortCode i generated in java using substring(0,3) which gives me PQR.
The problem is that in DB only one PQR should exist.

Which is it? Is the whole code "PQRS11" meant to uniquely identify your record, or just the first 3 characters "PQR"? You are identifying your target record uniquely using "PQR" so it sounds to me like "PQR" is your real primary key here, and "PQRS11" is just an attribute which also happens to be unique. Or maybe you need a separate table - one with the "PQR" short codes and whatever attributes belong to them, and another to hold whatever it is that "PQRS11" actually represents. You don't seem to have completely figured out what your records really represent or how they're uniquely identified here.


No more Blub for me, thank you, Vicar.
maganti suryanarayana
Ranch Hand

Joined: Mar 30, 2010
Posts: 53

chris webster wrote:
maganti suryanarayana wrote:
In my database i have the primary key as firstCode which is six digit long ex: PQRS11 and shortCode(Which is not a primary) which is three digit length:PQR
This firstCode i can create for various organizations, such as PQRS11, PQRS12,PQRS13. The shortCode i generated in java using substring(0,3) which gives me PQR.
The problem is that in DB only one PQR should exist.

Which is it? Is the whole code "PQRS11" meant to uniquely identify your record, or just the first 3 characters "PQR"? You are identifying your target record uniquely using "PQR" so it sounds to me like "PQR" is your real primary key here, and "PQRS11" is just an attribute which also happens to be unique. Or maybe you need a separate table - one with the "PQR" short codes and whatever attributes belong to them, and another to hold whatever it is that "PQRS11" actually represents. You don't seem to have completely figured out what your records really represent or how they're uniquely identified here.


Hi Chris, PQR is not an primary key chris. In DB only one PQR should exist either for PQRS11, PQRS12, PQRS13. Only one record in DB since there is an option to search the record either by PQRS11 or by three digit PQR. If PQR is assigned to PQRS11 and in future if the admin wants the PQR to be assigned to PQRS12 then PQR in DB should be deleted and should be assigned to PQRS12.

Thanks & Regards
Surya
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

maganti suryanarayana wrote:In DB only one PQR should exist either for PQRS11, PQRS12, PQRS13. Only one record in DB since there is an option to search the record either by PQRS11 or by three digit PQR. If PQR is assigned to PQRS11 and in future if the admin wants the PQR to be assigned to PQRS12 then PQR in DB should be deleted and should be assigned to PQRS12.


Is the SHORT_CODE always unique? From what you say, this is true i.e. SHORT_CODE must be unique.

Does every record have a value in the SHORT_CODE column e.g. "PQR"?

If both these criteria are true, then the SHORT_CODE is a good candidate for the primary key, and you could simply update the FIRST_CODE attribute when necessary e.g. if you had a record for "PQRS11" with SHORT_CODE "PQR", and you now want to have "PQRS12" instead:


If SHORT_CODE can be NULL, then it is not your PK but you can still put a unique index on SHORT_CODE as well (check if your RDBMS can handle NULL values here e.g. Oracle allows NULLs in UNIQUE columns but not for PKs), and let your application maintain this when a new record is inserted i.e. delete the old record if it exists and make sure you allow for the new record being the first one with the given SHORT_CODE. Obviously you need to do this BEFORE you insert the new record, but make sure you do it as part of the same transaction so you can back out the deletion easily if the insert fails. The unique index will not only prevent any duplicate SHORT_CODES but it should also speed up your deletion check e.g. if no records exist with the same SHORT_CODE, the query engine only has to check the index to establish this, not the entire table.
maganti suryanarayana
Ranch Hand

Joined: Mar 30, 2010
Posts: 53

Thanks Chris.

Regards
Surya
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Only one short code in DB
 
Similar Threads
Condition based on case in where clause
Primary Key Class - error
moving back and forth from jsp pages through servlet
Dynamic path configuration/resolving
Show the selected file and folder in tree structure with checkbox and the checkbox should be only..