UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Converting A Composite Key Into An Ordinary Primary Key, Any Version    
 
   
Javiator
post Sep 16 2019, 01:24 PM
Post#1



Posts: 351
Joined: 8-December 09
From: USA


Hello,

Help! I need the help of a math whiz...

After 10 years in operation, my application is going to be retired at the end of this year. Toward that end, we are moving to a third-party system, and I need to transfer records to it. The problem is that the new system requires a single unique identifier for something that my application has used a composite key of 2 fields. Basically, I'm looking for a function to turn a unique pair of integers into a unique 5-digit integer. The 5-digit integer must be unique for every unique pair of integers that is derived from, and the same 5-digit integer must always result from the same pair of integers. Am I making sense?

The two fields I am combining are SessionID and TraineeID. SessionID is always 4 digits in length, and TraineeID is between 1 and 5 digits.

As always, any assistance offered is much appreciated!

P.S. Our company is now using Office 365 Pro Plus.
Go to the top of the page
 
June7
post Sep 16 2019, 01:34 PM
Post#2



Posts: 889
Joined: 25-January 16



Could just use autonumber field to generate unique ID designated as primary key then add another field in dependent table and run UPDATE query to populate foreign key.

Does SessionID have leading zeros?

Maybe simply concatenate:

SessionID & TraineeID

or

SessionID & Format(TraineeID, "00000")

I think presence of an autonumber field may be required anyway depending on the platform migrating to.

This post has been edited by June7: Sep 16 2019, 02:00 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Sep 16 2019, 01:49 PM
Post#3


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


Why not use the AutoNumber as a Surrogate Primary Key to replace the current Composite Key? Add a new autonumber field, designate it as the Primary Key for the table, and put a unique index on the existing fields in the composite key so they remain unique.

You'll have to add the appropriate Long Integer field to child tables for the Foreign Keys in them and update that field using the new AutoNumber PK.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
kfield7
post Sep 16 2019, 02:15 PM
Post#4



Posts: 1,004
Joined: 12-November 03
From: Iowa Lot


The two fields I am combining are SessionID and TraineeID. SessionID is always 4 digits in length, and TraineeID is between 1 and 5 digits.
turn a unique pair of integers into a unique 5-digit integer

Not going to work. 4 + 5 > 5. You can't just take a piece of one and combine with the other to get consistent, unique values within the 5-digit constraint.
1234 + 432 could be 12342, 12343, 12344
1234 + 433 could be 12343, 12343, 12344
1234 + 443 could be 12343, 12344, 12344
etc.

Advice already given is good advice, where it fits.

Why the 5-digit constraint? Can you change the design of the new system?
Why the derived constraint? If it's for interchange of data between the two systems, but you only have design privileges in the old system, put your mapping table in the old system.

Can you provide info on what you can or cannot change in the new system?
Will you no longer have the two fields in the new system?
This post has been edited by kfield7: Sep 16 2019, 02:16 PM
Go to the top of the page
 
GroverParkGeorge
post Sep 16 2019, 03:02 PM
Post#5


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


One more observation is appropriate. What we are talking about is not the difference between a "Composite Key" and an "Ordinary Key". We are talking about the difference between a Natural Key and a Surrogate Key.

In your case, you have a natural key that consists of two columns--SessionID and TraineeID. It is the unique combination of those two values which allows you to designate them as the Composite Primary Key for this table.

The alternative being suggested is a new Surrogate Primary Key.

Both are, in fact, "ordinary" primary keys.

I also suspect that SessionID and TraineeID are, in fact, Foreign Keys in this table. SessionID would be related to the Primary Key in a table of Sessions, and TraineeID would be related to the Primary Key in a table of Trainees. Whether they are ALSO natural keys or surrogate keys in those other tables doesn't matter for this discussion. If they are here in this table and are being used TOGETHER to identify unique combinations of Sessions and Trainees in Sessions, they are Foreign Keys.

What has to happen is that you retain both of them, i.e. SessionID and TraineeID, in order to preserve those relationships with the two parent tables. Trying to jam together a calculated value to serve as a new Surrogate Primary Key in addition to those two Foreign Keys doesn't serve a very useful purpose, IMO. If you need that new Surrogate Primary Key to be 5 digits long to conform to some other restraint on the database, you can do that by padding them as needed.

But that brings up another problem, IMO. 5 digits will limit the number of possible records in this table to 100,000 records, i.e. 00000 through 99999. Is that enough? More than enough? Can you predict that?

I think, rather than invest time and efforts in mashing up a calculated value to serve as a Primary Key, the more efficient approach would be to use the native tools provided in Access to get the needed result with the least work, i.e. an AutoNumber.
This post has been edited by GroverParkGeorge: Sep 16 2019, 03:04 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
June7
post Sep 16 2019, 03:07 PM
Post#6



Posts: 889
Joined: 25-January 16



Just realized I read over the part about result needing to be 5-digit integer.

I agree, not feasible.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Sep 16 2019, 03:26 PM
Post#7


UA Admin
Posts: 35,898
Joined: 20-June 02
From: Newcastle, WA


I assumed that your SessionID and TraineeID are, in fact, natural keys based on the way they are described, although I guess they could also be AutoNumbers used as Surrogates in their respective tables.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 01:54 PM