ENUM can be a sensible.

Blough: TheRealGent: temp tables are dropped at the end of the connection.

Spruel: Xgc, a derived table? What would I be querying/

Stepien: TheRealGent: SELECT . FROM SELECT . AS derived_table LEFT JOIN .

Bouton: TheRealGent: That would be created with a UNION SELECT for each enum value

Island: Xgc, right, but the derived table query – you’re referring to the query against info schema?

Feldhake: TheRealGent: No. It’s totally inside the application to create that SQL part of the query.

Cezar: TheRealGent: That’s one simple way, without any duplication.

Clyatt: TheRealGent: Your application knows the list of the possible statuses at the time it sends that SELECT doesn’t it?

Stublaski: TheRealGent: and no need to touch the database.

Martell: TheRealGent: You said the java app has access to this java enum.

Drehobl: TheRealGent: The enum can even have a method to provide that SQL fragment.

Dingel: TheRealGent: Any report could use it without knowing the details.

Mura: That is not simple at all. That is horrendous. That implies 1 You’re using straight SQL/JDBC instead of ORMs or abstraction layers 2 You’d need to waste inordinate amounts of cpu cycles a day just iterating over enumeration values and dynamically generating strings that fill the heap unecessarily or 3 Design a caching system to avoid #2. None of which is “simple” or fun, or sane, or anything a dev in his right mind would like to do.

Bhatnagar: TheRealGent: Go away.

Voiles: Refuses to do the right thing and then whine about all the bad ways to get around doing the right thing.

Hitchings: There are other ways. Not going to waste the time.

Hoff: Xgc, don’t attack someone else’s critique of what you believe to be is correct.

Thoreson: I’ll go the outer join against info schema route. Thanks for the help guys.

Dingus: TheRealGent: YOU asked for other approaches. These were not suggested, until you refused to access the most appropriate.

Vandorien: Xgc, I did. And I heard you out, thanked, and settled on one of your proposed solutions. I just gave my own opinion on some of them, and obviously the last one didn’t sit well with your feelings/ego.

Clyatt: TheRealGent: You don’t want to waste precisous CPU cycles at your client app to prepare a mere string consisting of few values, but you are ready to waste CPU cycles at the database server to query Information_schema which in some cases can mean huge performance penalty for the entire server?

Dinges: Salle, and by entire server you mean one of the many abundant slaves that don’t have to support a mission critical app? Yes.

Hoen: TheRealGent: information_schema is a very bad idea, except for offline type operations.

Clyatt: TheRealGent: You managed to annoy the most patient and forgiving person here in this channel. That’s amazing achievement.

Magliano: The fact that he’s tied to some ORM is not material. That’s his choice and didn’t offer that at all in the discussion.

Strathman: Preferring an enum over a lookup table and getting mad about it.

Strathman: Changing the values in an ENUM requires ALGORITHM=COPY doesn’t it.

Korvin: Just the worst idea. Any first year student would have ruled out an enum for data that obviously needs to be in a list *inside the database* and accessible as a table.

Strathman: Yeah it does. Enjoy the downtime every time you want to execute DDL on that table to add a new ENUM type. It’s going to get painful once your table grows to a non-trivial size.

Brule: Xgc if you’re involved with Oracle/MySQL you should pitch the idea to deprecate the functionality.

Clyatt: TheRealGent: And changing the values in ENUM requires ALTER on your Master server and then wait for it to be replicated to all of your abundant slaves. Correct?

Weisenberger: TheRealGent: You don’t remove that as an option. You just teach people when to use which tools / constructs before they design databases.

Strathman: ENUM can be a sensible choice if you have a fixed set of possibilities that is not going to change over time.