Results 1 to 5 of 5

Thread: Primary Key

  1. #1
    Join Date
    Oct 2017
    Location
    Derbyshire, UK
    Posts
    3

    Question Primary Key

    Morning everyone,

    I am in the process of designing and developing a Database for the company I work for. They need 2 tables, one called 'Purchase_Orders' and the other 'PO_Invoices'.

    My question is concerning what to use for a Primary Key on the Purchase_Orders table. I was thinking of simply using the Purchase Order number which at present is simply an integer. My concern is what if at some point the company decides to change the format of their number?

    Should I create a completely seperate 'ID' column for the Primary Key and set this to Auto Increment?

    I need the Primary Key so I can set up a Foreign Key in PO_Invoices. I like the idea of using the PO Number because it makes finding linked information in the PO_Invoices table easier and more logical however if the format of the PO Number changes this will break the link.

    What is best practice on a matter like this?

  2. #2
    Join Date
    Dec 2015
    Location
    Guisborough, England
    Posts
    23
    I don't see any problem with using the PO number as the primary key - BUT, create the key as a string, NOT as an integer. Then there'll be no problem if characters are introduced later.

    Also, you don't say the size of the numbers now. If the numbers are such that the number of digits may increase sometime, then introduce leading zeros, such that PO 1234 becomes 001234. Or larger?

    You might even do it at PO001234, or PO/001234.

    Geoff

  3. #3
    Join Date
    Oct 2017
    Location
    Derbyshire, UK
    Posts
    3
    Thanks for the input Geoff,

    I have had problems creating a Primary Key which is a String in the past under MySQL and whilst I knew it was possible I was struggling. I will give it another go and see how I get on.


    Quote Originally Posted by GeoffB17 View Post
    I don't see any problem with using the PO number as the primary key - BUT, create the key as a string, NOT as an integer. Then there'll be no problem if characters are introduced later.

    Also, you don't say the size of the numbers now. If the numbers are such that the number of digits may increase sometime, then introduce leading zeros, such that PO 1234 becomes 001234. Or larger?

    You might even do it at PO001234, or PO/001234.

    Geoff

  4. #4
    Join Date
    Dec 2015
    Location
    Guisborough, England
    Posts
    23
    Yes, there are ways to do what I was suggesting, but they're not as convenient as I'd thought, especially if you're dealing with a Primary Key.

    Really, it depends on how BIG you're expecting the database to be.

    If it's going to be big, 10s or 100s of thousands of records or more, then you need to play by the MySQL rules, and focus on efficiency and performance, and take full advantage of the benefits of using primary keys properly (i.e. as intended within the structure of MySQL).

    This would prob mean leaving your primary key as numeric, and creating a separate field (column) with the char variant.

    If you feel that performance isn't a major issue, then you could create your main key on a constructed field (column), although it might NOT be a Primary key.

    Geoff

  5. #5
    Join Date
    Oct 2017
    Location
    Derbyshire, UK
    Posts
    3

    Thumbs up

    Thanks Geoff,

    You have given me things to think about.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •