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
> Using Non-sequential Numbers In Primary Key Field, Access 2013 Web App    
 
   
GrdnElf
post Sep 12 2017, 12:48 PM
Post#1



Posts: 23
Joined: 23-August 17



I'm tracking some bits of information pertaining to recruiting and hiring people for a contract (multi-year). The company's HR database creates a Job Requisition with a general description of the duties, and then a Job Position for the specific instance (location specific). So, I have a table (tblJobPositionNumReq) that has the Job Position Number (generated by the HR database), Job Position Posting Title (created by HR for the HR database, which includes the Role), Job Position Location (derived from the Requisition Title in tblJobReqs), Job Position Role (abbreviated form of Role in Posting Title), Job Requisition Number, and Position Nickname (an Abbreviated form of Job Position Role and Job Position Location that is used to create the 'Master Offer Letter Request' which is a PDF sent to HR requesting them to generate the official Offer Letter and send it to the Candidate.)

My question:

The Job Position Number is 8 digits, however they may not be sequential for the Job Positions that pertain to this contract. Will there be issues/weirdness in Access if I use the Job Position Number as the primary key for this table, or is it recommended to use a generated 'autonum' primary key (and have a separate field for Job Position Number)?

thanks in advance for any insights you can provide
Kate
Go to the top of the page
 
doctor9
post Sep 12 2017, 01:01 PM
Post#2


UtterAccess Editor
Posts: 17,583
Joined: 29-March 05
From: Wisconsin


Kate,

In my experience it's rarely necessary for a value like this to be a primary key. Your mileage may vary. I really like just using Autonumber because it means I don't have to do any error checking when a new record is created (did the user accidentally duplicate a record for an existing job position?), and I can just use a Long Integer foreign key in the related tables. If the user goes to create a new record, then hits Esc to cancel and close the form, everything is handled automatically.

Just because a value is unique, doesn't mean it MUST be the primary key. For example, when I started out with databases, I used a person's Social Security Number as a primary key. Then I found out that (1) SSN's are NOT actually unique, like we assume, and (2) HR wanted to really lock that value down, so I had to switch to an Autonumber EmployeeID field instead so the SSN was only in one place, where it could be protected.

TLDR: I'd personally make the job position number separate from the primary key.

Just my two cents,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
GrdnElf
post Sep 12 2017, 01:09 PM
Post#3



Posts: 23
Joined: 23-August 17



Doctor9

Thanks for the input.

I generally use the autonum for the ID, and have done so for all the other tables in the DB. The Job Position table was one of the first I created a few weeks ago when I created a prototype (mostly to understand what Access did with information). Now, I'm following the Michael Hernandez 'Database Design for Mere Mortals' book for my design review process and restructuring pretty much everything. I kept ruminating on this aspect and couldn't decide the 'best' course of action.

I like the rationale provided for your approach. It has helped me put the question to bed. Autonums all around :-)

Kate
Go to the top of the page
 
kfield7
post Sep 12 2017, 01:15 PM
Post#4



Posts: 733
Joined: 12-November 03
From: Conroe, TX


Agreeing with Doctor9, and would like to add: what if the rules (structure) of the job position number changes with time? What then happens to old data? this is in line with the example of SSNs not being unique.

You stated the JPN is always 8 digits? Does that mean the first 9,999,999 values are ignored? Or is it actually a text field? "00038423" I would have this text field long enough to accommodate potential changes in how the JPN is structured, although it's not difficult to lengthen a text field in Access, but you'd have to do that for every related foreign key field.

A numerical (long) field is also more efficient to process (sort, join, etc.) than a text field.
Go to the top of the page
 
Jeff B.
post Sep 12 2017, 01:20 PM
Post#5


UtterAccess VIP
Posts: 9,809
Joined: 30-April 10
From: Pacific NorthWet


I, too, will ask -- what are your business rules governing JPN. That is, not what Access or any other app claims are the limitations/constraints/"rules", but what does your business consider the purpose and constraints on a "Job Position Number"?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GrdnElf
post Sep 12 2017, 06:28 PM
Post#6



Posts: 23
Joined: 23-August 17



Thanks to kfield7, Jeff B who also chimed in...

Regarding business rules for Job Position Number: Since I don't use the corporate SAP database (and I'm not authorized to do so), I just get output from it via an Excel spreadsheet provided by our HR designated recruiter. All that I know is - We create the Job Requisition first by completing a SAP request form (with really bad UI design) which generates a 'request number' that then gets approved by the business unit VP, and when approved, the Req number is assigned/generated. Once the ReqNum is available, then we complete another SAP request form to create a request for a Position, that then gets approved by the business unit VP, and when approved, the PositionNum is assigned/generated. Since it appears to me that the SAP design assumption was that Requisitions are for a role in 'job category' and Positions are used to specify a specific location. IMHO - The designers didn't ask very many managers about how they think, or would want to use the DB, or think through the process very carefully. In any case, I'm stuck with how corporate has defined the process and DB output.

For managing the contract hiring, I need to be able to report to HR and my VP how many (and percent open/closed) of the Positions have Offers and subsequently people hired for them. The ReqNums are associated with the Position Nums so it can be derived how many Reqs are filled or open.

I too have considered the whatif the Position Num or Req Num field parameters change. Since PositionNum is all numeric, defining it as a number will allow for any number of digits. I'm concerned with Req Num because it is currently prefixed by 'NB' and 8 digits, so I have to define it as short text. I set the field size to 15, even though the current is 10, thinking that would give wiggle room for a few years.

other thoughts about dealing with future changes regarding field length?
Go to the top of the page
 
projecttoday
post Sep 12 2017, 11:53 PM
Post#7


UtterAccess VIP
Posts: 8,376
Joined: 10-February 04
From: South Charleston, WV


QUOTE
The company's HR database creates a Job Requisition with a general description of the duties,


Just to clarify, that would be SAP HR?

--------------------
Robert Crouser
Go to the top of the page
 
GrdnElf
post Sep 13 2017, 07:52 AM
Post#8



Posts: 23
Joined: 23-August 17



projecttoday,

Yes, SAP HR creates both the Requisition Number and the Position Number.
Go to the top of the page
 
doctor9
post Sep 13 2017, 08:13 AM
Post#9


UtterAccess Editor
Posts: 17,583
Joined: 29-March 05
From: Wisconsin


GrdnElf,

> defining it as a number will allow for any number of digits

I can see where you came up with this, but honestly, this sort of "number" is no more a number than a phone number or a zip code, based on your description. You should really define it as a Text field, as it's just a string of digits, not an actual number.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
GrdnElf
post Sep 13 2017, 08:22 AM
Post#10



Posts: 23
Joined: 23-August 17



doctor9 wrote

"> defining it as a number will allow for any number of digits

I can see where you came up with this, but honestly, this sort of "number" is no more a number than a phone number or a zip code, based on your description. You should really define it as a Text field, as it's just a string of digits, not an actual number."

OK re: 'just a string of digits' - I get it. Text it shall be then.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th September 2017 - 11:58 AM