2: Why would an index not help if there are only two options to choose from?
3: “ENUM is the spawn of the devil”
In MySQL: definately. MySQL doesn’t protect against duplicate options in the ENUM and when you remove an option that is in use, the records that have that option selected are NULLed. Yikes.
In other databases though… I’m not so sure. It depends on how it is applied, and why.
Generally I’d use a lookup when there is even a hint that the options might some day need to be changed by the end-user, or it is glaringly obvious that the options are going to be used for sorting (allthough a CHECK constraint can do that too) and when the value of the ENUM is linked to more information. The status for example is generally not 0 or 1, it is “active” or “inactive” and you will want to show “inactive” in the user interface, but store “0” in the database. ENUM can’t do that.
because the optimizer will ignore the index, since a table scan is likely faster
How does that follow from the fact that there are only 0’s and 1’s? The planner will look at how many records the index is likely to filter by looking at the statistics of how many 0’s and 1’s there are, not the fact that there are only 0’s and 1’s. (unless this is a MySQL thing, but I doubt that )
yes, any user changes to the ENUM values requires LATER TABLE privileges, which is not soimething you really want to offer users
Indeed, ENUM’s are meant for the DBA to lock the options in place.
Do you think you will ever take an SQL class and learn how to code databases properly? Do you do anything else the worst possible way or just your database tables?
Do you think you will ever take an SQL class and learn how to code databases properly? Do you do anything else the worst possible way or just your database tables?
I gues there is some history between the two of you? And if so; do you have to do that here?
No. I was just responding to a comment that indicate that the person who made it did not really understand SQL properlly but was attempting to give advice about it even though an SQL expert had previously identified that “3) ENUM is the spawn of the devil, please avoid it fastidiously”.and had then gone on to post links to pages that explain why you should NEVER use it.
No. I was just responding to a comment that indicate that the person who made it did not really understand SQL properlly but was attempting to give advice about it even though an SQL expert had previously identified that “3) ENUM is the spawn of the devil, please avoid it fastidiously”.and had then gone on to post links to pages that explain why you should NEVER use it.
In that case your reaction is way out of line.
As I discussed, politely and objectively, ENUM itself is not the spawn of the devil, it is a solution to a problem. If you have that problem, then ENUM is the solution. If you have a different problem, then ENUM is not the solution. So no, the poster you flamed is not wrong about using ENUM for yes/no situations, so don’t flame him for it.
All this is ofcourse moot when using MySQL because MySQL’s ENUM realy is the spawn of the devil, but then again, most of MySQL smells of brimstone
Actually, since we’re on the enum subject, maybe I can use the opportunity to learn some more.
When I need a “yes/no” flag, I’ve always used TINYINT and a 1 or 0. I use this in cases like whether or not a post in my blog is a draft or a final, whether or not a comment is approved or not, etc.
I guess I do this because I prefer to use numbers (it’s easier to type <cfif flag eq 1> than <cfif flag eq “y”> in ColdFusion) to strings, but maybe there is a better way to do this.