Stremlow: Xgc, you make it seem like they’re the crux of all DDL and database design.
Deguzman: Strathman, sounds pretty reasonable for a status no?
Wanda: And when you don’t need it as a table.
Clyatt: Strathman: . like hour_of_the_day for example
Strathman: TheRealGent: No, because it sounds like you’re going to change available statuses based on development changes over time
Doetsch: TheRealGent: Just because I don’t use enum for these cases doesn’t mean they can’t be used. But in your case, you obviously needed a construct usable as a table.
Keszler: There are ways around this. There always are. But, you know, there’s no reason to poke the monster again.
Clyatt: Strathman: You are missing his point though. It seems he believes it is “cheap” to ALTER TABLEs any time developers want to change something even if that ALTER is replicated over several slaves because it is “more expensive” to add another table for the same purpose
Strathman: Salle: Right and I’m trying to say that doing the ALTER TABLE is extremely Not Cheap.
Clyatt: TheRealGent: ENUM is fine when you know the list of possible values is not going to change any soon like columns called: gender, month, continent and similar
Strathman: I think the real solution to make everyone happy here is to use Postgres instead of MySQL for this while there’s still time to change :
Nolazco: Unfortunately not possible.
Gladhill: Salle, please tell me when I said it was cheap to alter tables.
Clyatt: Strathman: How is Postgres better for someone who wants single query and no joins? :
Strathman: At least it’ll do the rampant DDL transactionally
Clyatt: TheRealGent: You are telling that all the time I am afraid
Clyatt: TheRealGent: You said at the very begining you don’t want new table because it will make it more difficult for your developers to add new values didn’t you?
Kornn: Salle, no. Please read the messages again because that was not stated once. What was stated was I thought the extra joins necessary to work with the LK table would be expensive, compared to using enums.
Clyatt: TheRealGent So a dev adds a new status – 1 Add to Java enum. 2 Add to column enum. 3 Add to lk_table. Not to mention how do you do***ent this. Because the likely scenario is the dev will only do #1 and #2
Clyatt: TheRealGent: Didn’t you write that one?
Hanaway: Salle, are we talking cheap from an application or developer workflow point of view?
Clyatt: TheRealGent: I am talking about the point of view of entire application which includes both the database servers AND the client applcications and by the way includes also system maintenance and development workflow
Clyatt: TheRealGent: If you believe 10 minutes of developers worktime is more expensive than frequent downtime measured in hours for your entire system because of some ALTER then there is nothing more to talk about .
Perrigo: Salle, sounds like you’re just pulling at straws now. What are you trying to argue with me about now? We can go back and forth on this. How about you not accounting for all the extra execution time required for the join? Because across a cluster of systems running all the time, that does add up. Not to mention you’re making it seem like alterations to this variable are a common thing. Which they’re not.
Clyatt: TheRealGent: There is nothing to argue. There are decades of experience people around the world ac***ulated when using such kind of applications. You believe you know better. That’s not our problem. It is your problem.
Clyatt: TheRealGent: How long is the list of that possible values in your ENUM? 10? 100? 10000?
Clyatt: TheRealGent: How about extra execution time measured with microseconds for a join compared to YOUR wish to query Information_schema instead which can result in extra execution time measured in several seconds?
Montera: Salle, again, that is live production system vs offline reporting system.
Clyatt: TheRealGent: You want to query information_schema which is notoriously slow and can disturb other queries too instead of querying tiny small cacheable table?