1 Reply Latest reply on Jul 4, 2012 4:45 PM by philmodjunk

    2 or 3 text chars VS number as key for indexing and relation... what best?

    rs2.pt

      Title

      2 or 3 text chars VS number as key for indexing and relation... what best?

      Post

      Hi

      I am afraid that could be a very fulish question, the fact is that I have some large files that I imported to a filemaker project, those files are now tables with adresses, post codes, districts etc... this are oficial from our post offices in Portugal CTT (they can be downloaded from the oficial site).

      The thing is that they advise to keep some fields in Text, fields that are used as key fields, example: 01, 03, 12 etc... also the 3 digit post code like 001, 003, 010 etc ...that I use in relations.

      Is that more eficient? or will be better to change the field types to number. I have relations, indexings etc on those fields.

      On my last years on SQL databases I never had done key fields with text chars, I dont know if I never had to, or if it was best pratices... simply dont remember why.

       

      thanx

      Vitor 

      Portugal/Lisbon

        • 1. Re: 2 or 3 text chars VS number as key for indexing and relation... what best?
          philmodjunk

          I would guess that text was recommended as that is the easiest way to keep the leading zeroes. As long as smaller numbers are "padded" with leading zeroes so that all codes have the same number of digits, sorting by a text field will produce the same result as sorting on a number. Remove those leading zeroes and text will sort differently than number fields.

          If you put such codes into fields of type number, they might work just fine for searching and sorting, but you'd have to do some extra work to put back the leading zeroes as they would disappear from a number field.