|
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.
modified 1-Aug-14 12:42pm.
|
|
|
|
|
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, and Ctrl-C could be the termination of some process. 
Also, when I worked on a system that used characters, it was in Oracle, so we had case sensitivity. 
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! 
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.
|
|
|
|
|
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.
|
|
|
|
|
GUIDs for keys - ick!
According to my calculations, I should be able to retire about 5 years after I die.
|
|
|
|
|
I like GUIDs, but not for codes.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
modified 1-Aug-14 12:15pm.
|
|
|
|
|
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
|
|
|
|
|
Marc Clifton wrote: Personally I don't like that approach
I do, but I haven't gotten to write such an application for some time now. 
Marc Clifton wrote: redistributing the code when configuration information in the database changes
I don't think we're talking about configuration changes. In cases where I need to allow such flexibility, I wouldn't use an enumeration.
Adding/removing such codes as are being discussed in this thread is a change to the application (a new feature perhaps) so of course it has to be redeployed.
I'll use enumerations when it makes sense to do so and I'll generate them from the database at compile time (or before).
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
It should be a simple key referring to another table that gives further information about what the status means. Whether you use an int or a char for that field is not particularly important.
|
|
|
|
|
I create separate child table with values & store the ID value in transaction table(Chris way).
Transaction Table
----------------
Work Status
----------------
Work1 O
Work2 O
Work3 I
Work4 C
---------------- Child Table
----------------
StatusID StatusDesc
----------------
O Open
I In-Progress
H Hold
C Closed
----------------
Long 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 Char datatype 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).
|
|
|
|
|
SQL Server really needs to have an ENUM data type like MySQL.
Jeremy Falcon
|
|
|
|
|
Ummm... no. 
Well, OK, it should, but no one should use it, not even with MySql. It's the wrong solution to a non-problem.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
PIEBALDconsult wrote: Well, OK, it should, but no one should use it, not even with MySql. It's the wrong solution to a non-problem.
Not at all. It's a solution to a problem that exists if you care about, say it with me now... per form mance.
Jeremy Falcon
|
|
|
|
|
Jeremy Falcon wrote: if you care about, say it with me now... per form mance.
I do, therefore I would never use that; I'd use a regular translation table just like I can have with any other database and only translate when it makes sense to do so -- which is basically only needed in reporting -- certainly not needed by an application, where it would likely lead to needless operations and therefore reduced performance.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
PIEBALDconsult wrote: I do, therefore I would never use that; I'd use a regular translation table just like I can have with any other database and only translate when it makes sense to do so
Translation tables are useful for a variable / large amount of data, but for a quick short constant that's only particular to one table and not reused ENUMS are great. They're more meaningful than a number and they can lead to less DB clutter. They shouldn't be abused, but they have their place.
Jeremy Falcon
|
|
|
|
|
Jeremy Falcon wrote: a variable / large amount of data
That doesn't sound like a translation table.
Jeremy Falcon wrote: quick short constant that's only particular to one table
Yeah, like that. Small amounts of static data; as with status codes, transaction types, etc.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
PIEBALDconsult wrote: That doesn't sound like a translation table.
Using your terminology. I usually call them a look-up table, but when in Rome.
PIEBALDconsult wrote: Yeah, like that. Small amounts of static data; as with status codes, transaction types, etc.
Which is my point, SQL Server needs a type for that. A good example would be like a three state Boolean. Sure you can use 0, 1, and 2 but something like F, T, and * is much more readable.
Jeremy Falcon
|
|
|
|
|
Jeremy Falcon wrote: look-up table
Same thing -- look up the translation for some code. They were called translation tables when I was using Oracle in the 90s.
Jeremy Falcon wrote: SQL Server needs a type for that
I see no need to have a special type and all the added functionality it entails; it's just another table.
Jeremy Falcon wrote: F, T, and * is much more readable
Well, that's what this whole thread is discussing, but it seems you have a camp all your own  , right in between the others -- insert a character but actually store a number.
I work with a lot of databases, including MySQL, and I prefer to stay within the common areas as much as possible, and I need a pretty good reason to use something that only one database supports.
So sure, just as SQL Server recently added sequences (which Oracle has had for longer than I can recall), it seems like a reasonable feature to add -- to increase that common area.
But this particular feature seems like it could hurt performance with very little benefit -- I don't see how it can perform better than the current way that translation/look-up tables are used, and may be (slightly) worse.
As with SELECT *, I think it's a boon to interactive users who can benefit from a way to reduce command length and complexity (primarily by eliminating JOINs), but not something that an application benefits from. You've probably heard the arguments against SELECT * in code.
Performance is also not as big a concern with interactive users writing ad hoc queries as it is with 24/7 enterprise applications.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
PIEBALDconsult wrote: Well, that's what this whole thread is discussing, but it seems you have a camp all your own
Because this is mainly MS devs that never used an ENUM in a DB before. Of course I'm going to be "in a camp all my own" here.
PIEBALDconsult wrote: But this particular feature seems like it could hurt performance with very little benefit
You must be bored and just want to argue. Seriously, do I have to explain why a join on a look-up table is slower?
You're just arguing man. I use your term, you argue with that on semantics. I explain my term, then you explain to me why you use yours, which tells me we were on the same page by using your term in the first place and you know what I meant. Seriously, do you really like to argue that much?
Jeremy Falcon
|
|
|
|
|
Jeremy Falcon wrote: Of course I'm going to be "in a camp all my own"
I usually am too.
Jeremy Falcon wrote: do I have to explain why a join on a look-up table is slower?
Perhaps you could explain how it would implement the automatic translation/look-up without performing a JOIN behind the scenes?
Jeremy Falcon wrote: you know what I meant
You appeared to not know what I meant.
Jeremy Falcon wrote: do you really like to argue
No, but I want to be sure talking about the same thing. I think we are now.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
