Обсуждение: Clue to define a field data type
I have a table which will contain a set of pre-defined records defined by the government of my country. The fields are ID and DESCRIPTION. The ID field is composed of 3 characters that are always numbers. This means that I can set such a field as a char(3), varchar(3), smallint or integer, for example, and all of them will provide the correct functionallity. My question is, why should I define such a field as a character based type or a numeric based type? What is the best choice if this field will always be a 3-digit number?
Best regards,
Jorge Maldonado
--As of August 10, 2014 1:40:11 PM -0500, JORGE MALDONADO is alleged to have said: > I have a table which will contain a set of pre-defined records defined by > the government of my country. The fields are ID and DESCRIPTION. The ID > field is composed of 3 characters that are always numbers. This means > that I can set such a field as a char(3), varchar(3), smallint or > integer, for example, and all of them will provide the correct > functionallity. My question is, why should I define such a field as a > character based type or a numeric based type? What is the best choice if > this field will always be a 3-digit number? --As for the rest, it is mine. My opinion: Unless you are doing arithmetic, it's better to stick to a char field of some type. In this case especially, the fact that the ID is only numeric is incidental - at some future point someone could decide they need more than 1000 ID fields, and start using letters. (They could also decide to lengthen it instead.) The only reason to store them as numbers is that it would be slightly more space-efficient, but it would be *very* minor. My recommendation is to use varchar(3). Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
Daniel Staal wrote > --As of August 10, 2014 1:40:11 PM -0500, JORGE MALDONADO is alleged to > have said: > >> I have a table which will contain a set of pre-defined records defined by >> the government of my country. The fields are ID and DESCRIPTION. The ID >> field is composed of 3 characters that are always numbers. This means >> that I can set such a field as a char(3), varchar(3), smallint or >> integer, for example, and all of them will provide the correct >> functionallity. My question is, why should I define such a field as a >> character based type or a numeric based type? What is the best choice if >> this field will always be a 3-digit number? > > --As for the rest, it is mine. > > My opinion: Unless you are doing arithmetic, it's better to stick to a > char > field of some type. In this case especially, the fact that the ID is only > numeric is incidental - at some future point someone could decide they > need > more than 1000 ID fields, and start using letters. (They could also > decide > to lengthen it instead.) > > The only reason to store them as numbers is that it would be slightly more > space-efficient, but it would be *very* minor. My recommendation is to > use > varchar(3). I would use "varchar" and put the rest of the business logic into a check constraint. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Clue-to-define-a-field-data-type-tp5814371p5814406.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.