Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Location
    Iowa
    Posts
    35

    Unanswered: Creating an OrderID

    I am building an e-commerce site, and for my Order table I need primary key as well as an "OrderNo".

    For the primary key, I could just create an "ID" field and set it to Auto-Increment.

    If I take that approach, I have a solid PK, but that wouldn't work for my "OrderNo" because...

    1.) An "OrderNo" should be a fixed width value,

    2.) It would be awkward to have OrderNo = 1, OrderNo =2,..., OrderNo = 73129

    Is there some way to combine an "ID" field and an "OrderNo" field into one, but benefit from letting MySQL generate and increment the value for me, while not going 1, 2, 3...??



    TomTees
    Helping my wife to start an online business selling T-Shirts.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TomTees View Post
    Is there some way to combine an "ID" field and an "OrderNo" field into one, but benefit from letting MySQL generate and increment the value for me, while not going 1, 2, 3...??
    nope, sorry

    you really only need one unique column, not two

    if you're going to assign your own orderno, you won't need an auto_increment as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2010
    Location
    Iowa
    Posts
    35
    Quote Originally Posted by r937 View Post
    nope, sorry

    you really only need one unique column, not two

    if you're going to assign your own orderno, you won't need an auto_increment as well
    I understand that, but that isn't what I was asking...

    I don't want order numbers that go 1, 2, 3,...

    They should be fixed-width (e.g. 853021, 853022, 853023,...)

    And they should start at some number high enough so it doesn't look like we just started our business?!

    If I have an ID and use Auto-Increment, can I...

    1.) Start the Auto-Increment at a number higher than 1?

    2.) Pad the Auto-Increment Number so it is fixed width?


    If I cannot do those things, then what is the best way to generate an OrderNo so that it is reliable and there aren't any issues with concurrent users creating duplicate OrderNo's. (I'd guess that I need to use "Transaction Processing" in MySQL, right?)


    TomTees
    Helping my wife to start an online business selling T-Shirts.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TomTees View Post
    1.) Start the Auto-Increment at a number higher than 1?

    2.) Pad the Auto-Increment Number so it is fixed width?
    yes, an auto_increment can do both those things, all you have to do is insert the first row in the table with a sufficiently high id number, e.g. 857937, and they will increment from there


    Quote Originally Posted by TomTees View Post
    ... what is the best way to generate an OrderNo so that it is reliable and there aren't any issues with concurrent users creating duplicate OrderNo's. (I'd guess that I need to use "Transaction Processing" in MySQL, right?)
    the best way would be an auto_increment, and you do not need transaction processing to use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2010
    Posts
    1

    calculated field

    Personaly I think that OrderID can be a calculated field. I do not preffer the ideea of an iddentity...

    I do not know the size and architecture of your application... for complex applications with different distributed mysql databases... may be a bit confusing... or a big hard to setup (from administrative point of view)... to use iddentity.

    So, I think that you must can consider building a system, a formula for your order ID... like:
    LOCATION-OPERATOR_ID-ID... or anything else.

Posting Permissions

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