Results 1 to 9 of 9

Thread: string or int?

  1. #1
    Join Date
    May 2002
    Posts
    24

    Post string or int?

    what type of field should this requirement be?

    my company gives an numberical ID to each new physical building it does some work on by the following algorithm:


    ##-###
    <last two digits of the current year>-<sequential counter from 0>

    so for this year, the first building would be 02000
    followed by 02001
    and so on.

    basically, the leading zeros are significant. if i wanted to list all buildings from the year 2002, i would like to be able to do something like
    ID = '02*'.. if the ID field was an int, i would do ID >= 2000 AND ID <3000.

    all ID codes are always 5 digits.

    should the ID be a string or int field? any thoughts?

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You cannot maintain leading zeroes in an INT column, so you will probably have to use a CHAR field.

    You could store them like ".0200" but then you lose the trailing zeroes.
    Thanks,

    Matt

  3. #3
    Join Date
    Jul 2002
    Location
    Philippines
    Posts
    4
    Since this is a 5 digit variable i would suggest to declare two variables separated by 2 bytes "year" and 3 bytes "id" you can still use integer for the id part (e.g. int ID[0:2]). but the algo should make the ID start its counter to the "rightmost" (001) and "move to the left" whenever the incremental stage reaches an additional digit e.g. 10 or 100. Its not possible for string to be used as counter in which you need for the algo.
    Last edited by Aibhy; 07-26-02 at 01:46.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you make it an integer, the only benefit is that you can add 1 to get the next available number -- except you cannot do that anyway, because you start a new series every january

    make it char(6) and include the hyphen if it's commonly used, that way you won't have to mess with substrings on each and every query you write

    rudy
    http://rudy.ca/

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Store them as ints and use a derived view, e.g.

    CREATE TABLE Codes (year INT CHECK year < 100, code INT CHECK code < 1000, PRIMARY KEY (year, code), ...)

    CREATE VIEW CodesChar AS SELECT *, ( (RIGHT('0' + CAST(year as VARCHAR(2), 2) + RIGHT('00' + CAST(code as VARCHAR(3), 3)) AS code FROM Codes

  6. #6
    Join Date
    Nov 2002
    Location
    Oroville, CA, USA
    Posts
    2

    Re: string or int?

    A theorists answer:
    1. A field containing this numerical ID violates 1st normal form. BAD!
    2. If you must (you maybe want to keep your job), use string. Ints are for data that obey the rules of arithmetic. Two of these things will never be added, or multiplied together, but they will be compared for
    equality, which can be done for strings.


    Originally posted by fallacy
    what type of field should this requirement be?

    my company gives an numberical ID to each new physical building it does some work on by the following algorithm:


    ##-###
    <last two digits of the current year>-<sequential counter from 0>

    so for this year, the first building would be 02000
    followed by 02001
    and so on.

    basically, the leading zeros are significant. if i wanted to list all buildings from the year 2002, i would like to be able to do something like
    ID = '02*'.. if the ID field was an int, i would do ID >= 2000 AND ID <3000.

    all ID codes are always 5 digits.

    should the ID be a string or int field? any thoughts?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey pecondon, welcome to dbforums, if you are a theorist like me, you will love it here

    a small quibble, if i may --

    the original requirement was "last two digits of the current year plus a sequential counter from 0"

    you said "A field containing this numerical ID violates 1st normal form"

    first normal form is actually about repeating groups

    while the proposed building id is a truly ugly number, it does satisfy the requirement for first normal form

    rudy

  8. #8
    Join Date
    Nov 2002
    Location
    Oroville, CA, USA
    Posts
    2
    Maybe not 1st normal form, but it is not an "atomic" value. Different concept than 1nf, but equally basic.

    As to the sequentiality of assignment, I wonder how it is enforced. The numbering is done according to the order in which "work is done", not the order in which orders are taken, or the order in which orders are entered into the db, or whatever. I bet its rather messy in practice.

  9. #9
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: string or int?

    Originally posted by pecondon
    2. If you must (you maybe want to keep your job), use string. Ints are for data that obey the rules of arithmetic. Two of these things will never be added, or multiplied together, but they will be compared for
    equality, which can be done for strings.
    Hold on a second.

    If you're going to talk theory, talk theory. This "numerical id" is *not* a random string. It can't be "pecondon", for example.

    Speaking in terms of theory, we'd want a domain called "buidling id" that contained only the values that were valid building ids.

    Theoretically, this is a problem *completely* orthogonal to the relational model. It's about expressing a scalar value, not tables and joins.

    Since SQL doesn't have proper domain support, the best you can do is define a view that will enforce integirty. I'm not saying the example I gave is necessarily the best way, *but* because it uses declarative rules I think it would be simpler than something, say, based on triggers.

    The idea that "ints are for data that can be used arithmetically" is very poorly expressed. Integers are just values, like any other. How they're going to be used is immaterial when expressing the relation variable's predicate.

    first normal form is actually about repeating groups
    1NF simply states that the relation has a valid predicate. *All* relations are in 1NF by definition. (Not all SQL tables are relations.)

Posting Permissions

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