Results 1 to 6 of 6
  1. #1
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100

    What's Best Practice For PK Setting?


    Hi, I'm creating tables with unique values for normalization practices. At the moment, for example, I've created a table for FirstName. Column 1 is the PK set to auto number. Column 2 is FirstName, small text, no duplicates, no zero length. Reading the Access Bible, apparently, this is the normal practice. But in watching a tutorial video, the instructor has set column 1 (PK) with FirstName, Small text. No second column. So which method is best practice to save space in the design stage? So far all my unique value tables have column one (PK) with auto number while my second column also has the unique FirstNames.

    Also, in Table Design View, the instructor I'm watching is setting one of the fields to data type LookUp Wizard... Instead, I'm creating a FK field and linking (in the relationships window) it to its corresponding PK table. Again, which method should be used or preferred in design practice. Thanks.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    the instructor I'm watching is setting one of the fields to data type LookUp Wizard.
    The lookup wizard is an "implementation" by M$oft in MSAccess to market database to new users. It is specific to MSAccess and is NOT transferable to any other database management system. I'd watch it, then skip it --just be aware of the short coming.
    I suggest you watch several videos; do some "work through" tutorials, to appreciate the art and science of the process.
    Some Normalization references that may be helpful (-no particular order ---but they range considerably)

    Repeated Columns Issue
    Codd's Rules
    CJ Date Lecture
    Jerry Dennison An approach to Normalization
    Normal Forms
    Database Design RogersAccessLibrary
    Relational Database Series (Jennifer Widom)
    R937

    More complete info

    People Names are generally not good candidates for PK.

    Good luck.
    Last edited by orange; 08-27-2016 at 07:53 AM. Reason: additional link

  3. #3
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Thanks orange, I appreciate that. Will look through those links. Cheers.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    All tables needing a key should have it as an autonumber field with a unique name per table (do not repeat generic ID or Key as a name in every table). When the field is the foreign key in another table/field use the same name as its primary key name so you know it is the same field. No content field should ever be set as a key field - regardless of one's belief that it will always be unique. Always and only use autonumber. If there is tutorial video out there where the instructor has set column 1 (PK) with FirstName - they should be banned. You should leave a comment if possible to forewarn away other watchers from that advice.

    Only a few select specialty set of tables, typically referred to as Lists, do not necessarily need a key; such as: yes/no, States, Countries - these should be tables that almost never will change and serve as the source table for selectors or lookups in primary tables.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with orange and NTC.

    My naming convention:
    My primary key fields are ALWAYS autonumber type fields.

    If I have tables "tblCustomers" and "tblAddresses", the PK fields would be "CustomerID_PK" and "AddresseID_PK"

    Because "tblAddresses" needs a foreign key field to link to "tblCustomers", the FK field would be named "CustomerID_FK".

    This way there is no doubt which fields are PK and which are FK.

    Over time, you will develop your own naming scheme and, over time, it will change.


    You might also read these:
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers


    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp



    Good luck....

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So which method is best practice to save space in the design stage?
    I really wouldn't worry about space at this stage - space is cheap. You also have to think about indexes.

    Simplistically an index consists of the value (the primary key) and a pointer to the record.

    A numeric (long) index is 4 bytes long. text is 2 bytes per character. Not sure how big the pointer is but it doesn't really matter

    so you have a table with an autonumber primary key and a name text field. Say the name is 10 characters long

    so you have the primary key index (4 bytes) + name (20 bytes) + index (4 bytes) =28 bytes (plus pointer)

    if you just have the name as your primary key you have primary key index (20 bytes) + name (20 bytes)=40 bytes (plus pointer) - so actually, it takes up more space not having a primary key.

    Then you will have your child tables (e.g. products belonging to a supplier) - there will be many more of those (one supplier, a hundred products) - using a numeric primary key in your supplier table means you use a numeric family key in your products table - 4 bytes v 20 bytes - and again these family keys should also be indexed.

    So 'saving' one column actually means you will take up nearly 43% more space - plus all the issues of maintaining the relationships on spelling corrections, having two John Smiths and the like.

    Indexing is important and should not be neglected - take a look at post #6 of this link

    https://www.accessforums.net/showthr...d+wise+foolish

    and towards the end of this recent one where the lack of indexes had an enormous impact on performance

    https://www.accessforums.net/showthr...807#post331807

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

Similar Threads

  1. Replies: 1
    Last Post: 08-13-2016, 08:13 AM
  2. Best Practice Guidance
    By dpick in forum Access
    Replies: 2
    Last Post: 02-26-2016, 02:25 PM
  3. Best practice for data storage in db
    By BRZ-Ryan in forum Database Design
    Replies: 2
    Last Post: 03-05-2015, 08:07 PM
  4. Best Practice for Access, Macro, SQL
    By seageath in forum Database Design
    Replies: 0
    Last Post: 02-27-2012, 09:34 PM
  5. Best practice?
    By thekruser in forum Queries
    Replies: 2
    Last Post: 09-20-2010, 09:41 AM

Posting Permissions

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