Alpha Video Training
Results 1 to 13 of 13

Thread: Numeric fields vesus character fields

  1. #1
    Member
    Real Name
    Mike Nelson
    Join Date
    Mar 2008
    Location
    McAllen, TX
    Posts
    34

    Default Numeric fields vesus character fields

    I saw some posts indicating that using a numeric field is discouraged, even for instance as an ID field that autoincrements, but I do not understand why. I have several tables with numberic fiieds for the indexed field and wonder if I should change them, or if this is just a stylistic perference. I suspect not, but could someone exlain what are the drawbacks to this?

  2. #2
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,827

    Default Re: Numeric fields vesus character fields

    I think numeric is fine for hidden keys provided you are not going to take your application to the web. In the web EVERYTHING is a character, even your numeric fields. That means you constantly have to convert them in code when they are used on the web. That rule is not everywhere, so that makes it worse, having to figure out when you need to convert, when you don't.

    The only other drawback I see to numeric is that the width is not consistent if you start with 1. If I use a numeric for a key field in a Desktop application, I at least start it out with a number like 10000000 if my field is 8 wide.

    I have no idea if a numeric is faster for index and queries. Other's here have more depth on that subject.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  3. #3
    Member
    Real Name
    Mike Nelson
    Join Date
    Mar 2008
    Location
    McAllen, TX
    Posts
    34

    Default Re: Numeric fields vesus character fields

    Thank you so much, Steve. Now that I understand the reason, I will change my tables. If you please, I have a question about that. I changed the field to character, same width, and the auto-increment jumped to 90000. The previous values had been 258 in one table and 33 in another. Is there any way to make the new character some number (character) that is more in line with my old ones, or would I have to go back and renumber (character) all the previous entires? I mean for instance if '1' is the first record, change it to 90001? Or other than aesthetically, does it make any difference and not worth the effort?

  4. #4
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,827

    Default Re: Numeric fields vesus character fields

    Autoincrement is 'smart' and it looks at your entire set of values for that field and incriments the number for the next record. Its character, so "5" is greater than "400",and so on. If you are in a position where renumbering is OK, then renumber them all starting at "10000" (if your field size is 5).

    First, make the field size 5 with no field rules. Go to Operations and make a new Update operation, then Assign Serial Values. Use this to populate your table with new values, making sure you fill the field length. By the way, if possible, its best to use a alpha at the start of your value, such as C00001. This prevents the web app from assuming your value is a numeric, which can happen if you assign the value to a variable, but have not DIM'd the variable as a character first.

    Now go back to the table and set the field rules for that field to autoincrement.

    FYI, autoincement is just one way to create a unique value for a record, and not always the best way. You can also use TIME() function plus a random number, or apply a UUID.

    IF you cannot renumber, then you do have to use that 90000 value as your next highest number and start from there.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  5. #5
    Member
    Real Name
    Mike Nelson
    Join Date
    Mar 2008
    Location
    McAllen, TX
    Posts
    34

    Default Re: Numeric fields vesus character fields

    Thank you so much, Steve. That was a wealth of information. As I grow in knowledge, maybe I can help others as well, as you have done for me. Thank you again.

  6. #6
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Numeric fields vesus character fields

    Hi Mike,

    I'll disagree a bit with Steve here. Despite that the web is much more character based, I believe that all data should be stored in a type that is closest to it's base meaning.

    Numeric fields should generally be used with things that numeric operations would be performed on, e.g. amounts, quantities, etc. Converting, using VAL() and STR(), LTRIM() and PADL() functions is not that big a deal.

    E.g.
    • numb=VAL("1234.56")
    • char=PADL(LTRIM(STR(1234.56,25,2))),8,"0")
    Numbers that should be stored as character include license #'s, Social Security #'s, Phone #'s and the like, where they are not really numbers to be calculated with.

    Date fields should be date type unless partial dates are sometimes given, e.g a person's birthdate like 07/04 where they don't want to give the year. In that case you should use a character field which can have a template and allow blank portions of a date so that you can record what you do know.

    Logic fields should be logical types.

    The exception for all of these is when you need the concept of a null field (essentially blank) and that type can not store and test for a null, e.g.
    • True, False, unspecified
      (I use a 1 character field to store blank, T or F)
    • A numeric field where null is a different meaning than 0
    Date fields can have a blank date, and tested for it, so that is not an issue

    Autoincrement fields seem to be better (in terms of problems with Alpha 5) with Alpha 5 Desktop applications as characters, although I have used both character and numeric. Converting back and forth and padding of leading zeros is not difficult. as shown above.

    However, according to Selwyn, he says there are some flavors of SQL tables that only allow numeric autoincrement fields, so that might be somewhat an issue with some tables and you should consider that carefully.

    Alpha has a wide variety of functions to convert between formats, so the conversion should not be overly difficult.

    Hope this helps!
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



  7. #7
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,827

    Default Re: Numeric fields vesus character fields

    I agree with everything Ira said. I just was not as detailed in my response. I use Logical, Date and Numeric where warranted, but never in the key field. In fact, I most often use the UUID for my key field as they are easy to create and never duplicated. I don't like autoincrement for key fields because it IS possible to get a duplicate.

    Let me clarify, if a record is created with autoincrement 10000 for Steve. Then for some reason record 10000 gets deleted. Autoincrement will give the next record that same 10000 value and presumably assign it to someone else. No harm unless there had been some transaction activity while record 10000 was assigned to Steve. Now you have transaction history where record 10000 represents information on both Steve and that other guy. In theory you would not allow such a record to be deleted, but in practice, records get deleted by unwitting admin users.

    So I use remspecial(api_uuidcreate)) to create UUID values instead of autoincrement. The worst you get is orphaned records.

    The only problem with a UUID is that it is really big! So I also use time("yyyymmdd0h0m0s3"+upper(rand_string(2))).
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  8. #8
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Numeric fields vesus character fields

    Hi Steve,

    Quote Originally Posted by Steve Wood View Post
    I agree with everything Ira said.
    Then I guess you are disagreeing with me that you and I disagree a bit but at the same time you agree with everything I said. Oops Infinite Loop, Infinite Loop, Infinite Loop, Infinite Loop, Infinite Loop, Infinite Loop,.......


    :D
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



  9. #9
    Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,827

    Default Re: Numeric fields vesus character fields

    Yes, and I run for public office next year.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  10. #10
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,699

    Default Re: Numeric fields vesus character fields

    I generally agree with Steve and Ira, but you do need to keep in mind both users, and the possibility of moving to a SQL database.

    Peter Wayne recently commented that converting his application to SQL was more difficult because he had always subscribed to using character key fields and most? flavors of SQL do not allow character primary keys. So he was moving to numeric autoincrement fields.

    I have historically used character id fields with leading zeros. That can be a hassle for users if the id is "0000001" or even "1000001". A user is much more accurate if they only need to type 1 or 56 or 1234, without keeping track of the total number of characters involved.

    As Ira points out, if 1, 56, and 1234 are character values, then an index can use an expression str(myidfield), converting the values to " 1", etc. so they are in correct sort sequence. But then the user must enter leading blanks when using find by key... and that will never work.

    So early in my Alpha life I used numeric key fields, then for a long time used character key fields. Going forward I would look more favorably on numeric key fields for the benefit of users and SQL compatibility, and then do any required conversion in code.

    I also use UUIDs to guarantee uniqueness (in my case across different databases), but am told that these should never be used in SQL clustered indexes - sever performance impact. So if a UUID is used to guarantee uniqueness, then a different field should be chosen as the primary key.

    What all this means is that there is not one best answer, and you don't have to think about it any more. Knowing some of the ramifications does help make an informed decision.

    Bill.

  11. #11
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Numeric fields vesus character fields

    Hi Bill,

    Quote Originally Posted by Bill Parker View Post
    But then the user must enter leading blanks when using find by key... and that will never work.
    That should never be a problem.

    In any application, a user should never be entering in a Find by key box. He should input a find value, which an appropriate UDF function for the application should convert to the current field, type, format and width needed for the current structure. This isolates the table structure from the user, who should never have to care. If the width changes becauses the field needs to grow, changing the UDF will allow all places to change with it.

    Better yet, it's even possible (as you know) to get the structure information and adapt the width dynamically in the UDF as needed.
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



  12. #12
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Numeric fields vesus character fields

    Hi,
    Just a bit of extra info.
    Another thread answered by Stan Mathews ( http://msgboard.alphasoftware.com/al...ad.php?t=84148 ) shows that if the month is wanted to be represented in Steve Woods example code of post 7 it should be as follows
    mm is not a valid format string as used. Should be

    time("yyyyMMdd0h0m0s3"+upper(rand_string(2)))

    Well, actually it is a valid format string but it would be valid if you wanted two representations of minutes.
    Just wanted the threads to be a bit more tied together and eliminate other possible confusion on this.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  13. #13
    Member
    Real Name
    Ron Leunis
    Join Date
    Sep 2005
    Location
    Netherlands
    Posts
    652

    Default Re: Numeric fields vesus character fields

    Totally agree with Ira's :

    "I believe that all data should be stored in a type that is closest to it's base meaning".

    Presenting data should be separated from storing data.

    Ease of presentation should not influence how to store data types.

    You datamodel (for a certain business area) with its datatypes (closest to its base meaning) will have a longer lifecycle than apps using a certain technigue to present them.

    regards, Ron

Similar Threads

  1. Formatting Numeric fields
    By Graham Wickens in forum Alpha Five Version 6
    Replies: 4
    Last Post: 08-26-2005, 06:29 AM
  2. Numeric fields - too many digits
    By Jos Schaars in forum Alpha Five Version 6
    Replies: 3
    Last Post: 07-31-2004, 10:04 AM
  3. Totaling Numeric fields
    By David Sabol in forum Alpha Five Version 5
    Replies: 0
    Last Post: 01-08-2003, 04:32 AM
  4. indexing numeric fields
    By Robert Uyttebroek in forum Alpha Five Version 5
    Replies: 2
    Last Post: 12-26-2002, 05:10 PM
  5. Numeric Fields
    By Bernard Posner in forum Alpha Five Version 5
    Replies: 2
    Last Post: 09-03-2002, 07:16 PM

Bookmarks

Posting Permissions

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