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
SQL Server really needs to have an ENUM data type like MySQL.
Jeremy Falcon
-
SQL Server really needs to have an ENUM data type like MySQL.
Jeremy Falcon
Ummm... no. X| 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.
-
Ummm... no. X| 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
-
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.
-
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
-
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.
-
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
-
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 :-D , 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 againstSELECT *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.
-
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 :-D , 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 againstSELECT *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
-
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.
-
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.
PIEBALDconsult wrote:
Perhaps you could explain how it would implement the automatic translation/look-up without performing a JOIN behind the scenes?
There is no look-up with an ENUM data type. It's just a data type. Instead of using a number with no inherit value, with no referential data checking for a rogue number, etc. you can use strings. Not only is there no look-up, which is fast, this has so many advantages with automatic data checking / integrity (even in the case of a tri-state Boolean) it's unreal.
PIEBALDconsult wrote:
You appeared to not know what I meant.
I did. And like I said, ENUM types fall short on variable length rows of values / types / whatever you want call them. And if the look-up needs to be re-used they fall short. But for a one-time small item, such as tri-state Boolean they're awesome.
PIEBALDconsult wrote:
I think we are now.
I've always been talking about this yo.
Jeremy Falcon