Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Only one short code in DB

 
maganti suryanarayana
Ranch Hand
Posts: 53
Java MyEclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Maneesh Godbole
Saloon Keeper
Posts: 11021
12
Android Eclipse IDE Google Web Toolkit Java Mac Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to a more suitable forum...
 
chris webster
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
maganti suryanarayana
Ranch Hand
Posts: 53
Java MyEclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 53
Java MyEclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Chris.

Regards
Surya
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic