Database - Use number or character?
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
Letters.
julian@giant wrote:
My preference is number, so that they can easily be turned into an enum at the code end.
It shouldn't be the consideration. User ease, understand ability is what should be presented. You code around that. Coding ease should never be the deciding factor. User experience should always be the reason to present things.
**_Once you lose your pride the rest is easy.
I would agree with you but then we both would be wrong._**
The report of my death was an exaggeration - Mark Twain Simply Elegant Designs JimmyRopes Designs
I'm on-line therefore I am. JimmyRopes -
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
A number, with a related status table holding the number and a text description, so it can be displayed in reports etc.
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
I'm with Chris - number and related descriptive table.
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952) Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
-
A number, with a related status table holding the number and a text description, so it can be displayed in reports etc.
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
That's how I've always done it as well. However, the place I contract for right now has some status tables and they use the characters with a text description, instead of a number.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
This is the wrong forum, but use a number, with a translation table -- that way you can use referential integrity, and an enumeration in code. Edit: Additionally, numbers allow for a hierarchy of values, so you can fit more information in the values -- not easy to do with single characters. Edit 2: And globalization. Edit 3: Single-letter codes lead to the problems that are prevalent with command-line applications that take single-character (case sensitive) options. E.g. "F" means "file", "f" means "format", and that means we need to use "v" for "filter", and because "V" means "view", we'll need to use "w" for "virtual"...
You'll never get very far if all you do is follow instructions.
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
I normally allways go for a status table, with number and text values per status. This allows for a greater range of statuses, but things also depend on how much human insight and or intervention required in processing that data. A single letter is always more readable, unless it is easily confused with other values or other domains.
-
That's how I've always done it as well. However, the place I contract for right now has some status tables and they use the characters with a text description, instead of a number.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
Then don't rock the boat. Follow the standard that's in-place. I worked at a place that did that too, which is why I'm even more convinced that numbers are best.
You'll never get very far if all you do is follow instructions.
-
This is the wrong forum, but use a number, with a translation table -- that way you can use referential integrity, and an enumeration in code. Edit: Additionally, numbers allow for a hierarchy of values, so you can fit more information in the values -- not easy to do with single characters. Edit 2: And globalization. Edit 3: Single-letter codes lead to the problems that are prevalent with command-line applications that take single-character (case sensitive) options. E.g. "F" means "file", "f" means "format", and that means we need to use "v" for "filter", and because "V" means "view", we'll need to use "w" for "virtual"...
You'll never get very far if all you do is follow instructions.
You can get referential integrity with a status table that uses a single char as PK against other tables using the same as FK, and it's more readable. You only lose the enumeration. And only 230 other possible values for 1 byte of storage.
-
Then don't rock the boat. Follow the standard that's in-place. I worked at a place that did that too, which is why I'm even more convinced that numbers are best.
You'll never get very far if all you do is follow instructions.
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
Number with a status stable should allow for regionalization better. Also, there may be confusion with what a letter stands for. I - In progress or incomplete C - Complete or cancelled Tim
-
You can get referential integrity with a status table that uses a single char as PK against other tables using the same as FK, and it's more readable. You only lose the enumeration. And only 230 other possible values for 1 byte of storage.
Brady Kelly wrote:
can get referential integrity
True. But the use of characters may lead to laziness and therefore no such table, whereas it's much more important when using numbers.
Brady Kelly wrote:
it's more readable
Not to the user -- who should never see the codes. The computer doesn't care and the developers shouldn't either.
Brady Kelly wrote:
230 other possible values
Don't sell yourself short; you're not limited to alphabetic characters -- a status of
(could be the start of something and]the end of something else, andCtrl-Ccould be the termination of some process. :-D Also, when I worked on a system that used characters, it was in Oracle, so we had case sensitivity. :omg: I could never remember the codes so I always had to look them up anyway. Where I am now I use numeric codes and keep a print-out of the translations pinned to my wall. Oh, oh, oh, I just remembered -- one of the applications I maintain a little bit here uses GUIDs for all foreign keys, including things like type and status values! :laugh:You'll never get very far if all you do is follow instructions.
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
I prefer to keep state in separate table with an FK in whatever other tables need to reference the record state. So, we're really just dealing with ID's, and then you can put whatever short description, long description, "token" char/value, into the lookup table for the UI and it can be easily changed.
julian@giant wrote:
so that they can easily be turned into an enum at the code end
Yuck. That locks your code with to your data/state. What if some day the user wants a new state, like "Cancelled", "Deleted", "Under Review", whatever? The code should look up all the current possible states from the database! If you have code that does something specific because of state, put it in a stored procedure if possible on the DB side or a separate DLL for application specific stuff on the client side, unless it's a web page in which case the whole maintenance model is somewhat different. Marc
Latest Articles - APOD Scraper and Hunt the Wumpus Short video on Membrane Computing Hunt the Wumpus (A HOPE video)
-
Brady Kelly wrote:
can get referential integrity
True. But the use of characters may lead to laziness and therefore no such table, whereas it's much more important when using numbers.
Brady Kelly wrote:
it's more readable
Not to the user -- who should never see the codes. The computer doesn't care and the developers shouldn't either.
Brady Kelly wrote:
230 other possible values
Don't sell yourself short; you're not limited to alphabetic characters -- a status of
(could be the start of something and]the end of something else, andCtrl-Ccould be the termination of some process. :-D Also, when I worked on a system that used characters, it was in Oracle, so we had case sensitivity. :omg: I could never remember the codes so I always had to look them up anyway. Where I am now I use numeric codes and keep a print-out of the translations pinned to my wall. Oh, oh, oh, I just remembered -- one of the applications I maintain a little bit here uses GUIDs for all foreign keys, including things like type and status values! :laugh:You'll never get very far if all you do is follow instructions.
I'm just thinking back to my SAP days, as a novice developer. There, many codes can and do spill over into user space, and I found it quite easy remembering four letter transaction codes and so on, versus having to choose from a hidden grid or dropdown somewhere in the great unknown.
-
I'm just thinking back to my SAP days, as a novice developer. There, many codes can and do spill over into user space, and I found it quite easy remembering four letter transaction codes and so on, versus having to choose from a hidden grid or dropdown somewhere in the great unknown.
Brady Kelly wrote:
four letter transaction codes
Hey now, that's just crazy talk. And it doesn't stop you from having a numeric code, a long description, and a four-letter abbreviation.
You'll never get very far if all you do is follow instructions.
-
I prefer to keep state in separate table with an FK in whatever other tables need to reference the record state. So, we're really just dealing with ID's, and then you can put whatever short description, long description, "token" char/value, into the lookup table for the UI and it can be easily changed.
julian@giant wrote:
so that they can easily be turned into an enum at the code end
Yuck. That locks your code with to your data/state. What if some day the user wants a new state, like "Cancelled", "Deleted", "Under Review", whatever? The code should look up all the current possible states from the database! If you have code that does something specific because of state, put it in a stored procedure if possible on the DB side or a separate DLL for application specific stuff on the client side, unless it's a web page in which case the whole maintenance model is somewhat different. Marc
Latest Articles - APOD Scraper and Hunt the Wumpus Short video on Membrane Computing Hunt the Wumpus (A HOPE video)
Marc Clifton wrote:
a new state, like "Cancelled", "Deleted", "Under Review",
That's an application change -- "new features".
Marc Clifton wrote:
look up all the current possible states from the database
Yes, at compile time, and generate the enumeration therefrom.
You'll never get very far if all you do is follow instructions.
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
-
Marc Clifton wrote:
a new state, like "Cancelled", "Deleted", "Under Review",
That's an application change -- "new features".
Marc Clifton wrote:
look up all the current possible states from the database
Yes, at compile time, and generate the enumeration therefrom.
You'll never get very far if all you do is follow instructions.
PIEBALDconsult wrote:
Yes, at compile time, and generate the enumeration therefrom.
Personally I don't like that approach, simply because it requires redistributing the code when configuration information in the database changes, and probably not worth arguing the pros and cons unless we find ourselves working together on a project. :) Marc
Latest Articles - APOD Scraper and Hunt the Wumpus Short video on Membrane Computing Hunt the Wumpus (A HOPE video)
-
Brady Kelly wrote:
can get referential integrity
True. But the use of characters may lead to laziness and therefore no such table, whereas it's much more important when using numbers.
Brady Kelly wrote:
it's more readable
Not to the user -- who should never see the codes. The computer doesn't care and the developers shouldn't either.
Brady Kelly wrote:
230 other possible values
Don't sell yourself short; you're not limited to alphabetic characters -- a status of
(could be the start of something and]the end of something else, andCtrl-Ccould be the termination of some process. :-D Also, when I worked on a system that used characters, it was in Oracle, so we had case sensitivity. :omg: I could never remember the codes so I always had to look them up anyway. Where I am now I use numeric codes and keep a print-out of the translations pinned to my wall. Oh, oh, oh, I just remembered -- one of the applications I maintain a little bit here uses GUIDs for all foreign keys, including things like type and status values! :laugh:You'll never get very far if all you do is follow instructions.
GUIDs for keys - ick!
According to my calculations, I should be able to retire about 5 years after I die.
-
What are people's preferences for using numbers or letters for a field that indicates the status of something? If something is 'In Progress' you might use the letter 'I' in a Char field or a 1 in a number field for instance. As soon as the record is 'Complete' it get updated to a 'C' or a 2 for instance. My preference is number, so that they can easily be turned into an enum at the code end. It causes arguments at work like you wouldn't believe....! Anyone care to make a view? Julian
I create separate child table with values & store the ID value in transaction table(Chris way). Transaction Table
----------------
Work StatusWork1 O
Work2 O
Work3 I
Work4 CChild Table
----------------
StatusID StatusDescO Open
I In-Progress
H Hold
C ClosedLong time ago, I have used bit datatype for 2 value columns(0 or 1). But later I had to change insert one more value based on requirement. After that, I never used bit datatype. Even for simple things(like Gender, Marital Staus) I use Child table like above. Currently I use
Chardatatype so I could use many values. In number datatypes you can't store any characters & number values are not easy to recognize. If it's character datatype it's easy to recognize(Like C for Closed, O for Open & so on).thatraja
