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.

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/

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

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?

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

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.

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