Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Numeric fields vesus character fields

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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)

    Comment


    • #3
      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?

      Comment


      • #4
        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)

        Comment


        • #5
          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.

          Comment


          • #6
            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


            Comment


            • #7
              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)

              Comment


              • #8
                Re: Numeric fields vesus character fields

                Hi Steve,

                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


                Comment


                • #9
                  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)

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Numeric fields vesus character fields

                      Hi Bill,

                      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


                      Comment


                      • #12
                        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
                        __________________________________________



                        Comment


                        • #13
                          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

                          Comment

                          Working...
                          X