Results 1 to 11 of 11

Which column should be selected as the Primary Key in a database

  1. #1
    mirhamxa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2019
    Posts
    3

    Which column should be selected as the Primary Key in a database

    I am creating a database for the project of my school. It will not be used for my school, but this only an assignment. I just wanted to know, while creating a database for the students, which column should be the primary key?



    The roll number assigned by the school?
    The national identity card number?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,100
    If the roll number is guaranteed to be unique to a student, I'd use that. That's partially my hesitancy to use an identity number that sounds like a US social security number, which could introduce privacy concerns. If you use the national number, I wouldn't expose it unless I had to.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mirhamxa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2019
    Posts
    3
    That is why I was confused about using the national number. So, roll number could be the primary key.
    Thanks.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,100
    Only if it's unique and I should have added can't change (like my roll number is 123 this year and 456 next year).

    Welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,247
    My PK field is always an autonumber type and every table has one.
    Maybe this will help.... Microsoft Access Tables: Primary Key Tips and Techniques
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    mirhamxa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2019
    Posts
    3
    Yes, got that. The pattern of roll number includes the year. Like all the students who got admission in 2018 will have 2018 at the start of their roll number. Like if I got the roll number 129 in 2018, then my complete roll number would be 2018-129 and it will remain the same each year. The next year there might be another person who got the number 129 but his complete roll number will be 2019-129.

    So it will remain unique and the same for individuals.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,864
    I probably wouldn't use it. The school could alter their scheme any year they want to, even to the point of re-assigning current students new numbers in a new schema. Then there are other possible variables, like what happens if you drop out next year and come back the year after? Now you're 2021-129? 2021 + some new number? I might make it a unique + required index, but not likely the PK since there's no compelling reason to make it a PK when you could use something else that guarantees uniqueness.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  8. #8
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,460
    Disagree completely.
    I WOULD use the admission number (or roll number if you prefer) though if there is a national unique pupil number (UPN) that would be equally valid.

    This is one time that using an autonumber is in my view utterly pointless as there is a unique number (or text) value already available
    In addition, it is very likely that data would be imported from the central school or local area database so you should use the existing primary key value(s)

    In many years of experience working in UK schools, they do not change admission number systems for students already on roll.
    If for any reason the system is changed, it is applied to new admissions for the next academic year but NOT for existing students.
    I would be very surprised it that were different in other locations.

    Readmissions will continue to use the original admission number as, of course, their data remains (archived) on the central school database
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,864
    The roll number assigned by the school?
    The national identity card number?
    I guess those phrases mean something to you. They mean nothing of the sort to me, and the notion that a whole country would have the same enrollment system didn't occur to me. My bad.

    I guess that comes from living in a country that's 10 million square kilometers versus one that's only 130,000.
    Pretty impressive to have one system when England has about 1.7 times the population of Canada.

  10. #10
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,026
    Ah but in Canada you have to really shout to be heard in the next community - in the UK you only have to whisper

  11. #11
    isladogs's Avatar
    isladogs is offline Very intense programming
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,460
    Actually in the UK we have at least 4 different systems for England, Scotland, Wales & NI

    There is indeed a national Unique Pupil Number (UPN) as well as another unique number called the Unique Learner Number (ULN) used for exams.
    Never understood why both were needed!
    However each school will define their own admission number (AdNo) system e.g. 12345 or 012345 or A12345
    When a student transfers schools a common transfer form (CTF) is forwarded to the new school and this will include the UPN.

    Doncha' just love acronyms
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 10-21-2017, 04:05 PM
  2. Replies: 4
    Last Post: 09-28-2017, 11:47 PM
  3. Replies: 4
    Last Post: 07-27-2016, 12:07 PM
  4. Replies: 2
    Last Post: 07-10-2013, 12:01 PM
  5. Using AutoNumber primary key on sorted column
    By grkatz823 in forum Access
    Replies: 7
    Last Post: 03-04-2013, 03:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums