Clyatt: TheRealGent: Not to mention that ENUM always allows entering empty string ” value
Clyatt: TheRealGent: You need that join no matter if you add this new table or not
Hechinger: TheRealGent: There’s no good alternative in a relational database. You want lists to be in tables.
Zanders: Salle, I’d need a join on this reporting query. THat’s a huge difference from altering the DDL on the production application schema.
Paguin: TheRealGent: It’s almost no change to your schema.
Deloye: An application doesn’t need this.
Clyatt: TheRealGent: You need that join no matter if you add this new table or not. No kidding. Xgc also tried to explain the same to you
Kali: It wants to do simple CRUD on one table most of th etime.
Tarantino: TheRealGent: You don’t need a separate key. Just use the natural string as a key.
Santaana: Salle, can you explain why I’d need a join even if I didn’t have a new table?
Chanthaumlsa: TheRealGent: No other table need be touched, unless you want to add the FK constraint.
Clyatt: TheRealGent: If you want the server to return 0s for missing values you do need a join. Fullstop
Emigh: TheRealGent: Your initial question requires an outer join.
Twillie: Oh my Lord, I understand that!
Clyatt: TheRealGent: If you don’t want it then simply do it at the client side which does know all the enum values already and has no problem to add these 0s to the result
Peoples: Please can we come to a consensus that I understand that?
Clyatt: TheRealGent: You understand what exactly?
Stoeltzing: TheRealGent: You just asked why you would need a join?
Clyatt: TheRealGent: theRealGent salle, can you explain why I’d need a join even if I didn’t have a new table?
Clyatt: TheRealGent: This last question doesn’t imply you understand why do you need a join in any case
Yonashiro: TheRealGent I’m not against using joins cause they’re joins. Adding an LK table would mean that all application queries would contain these joins to do simple things like look ups. Unless cached
Rumphol: TheRealGent: As salle suggested, you can avoid the join and fill in the missing data application side.
Wiedeman: TheRealGent salle, I’d need a join on this reporting query. THat’s a huge difference from altering the DDL on the production application schema.
Francescon: TheRealGent: Ok. Stop. What are your alternatives? Are any of them acceptable to you?
Demarcus: Xgc, because this is a reporting query on a slave that runs periodically, I will join against the information schema table if I can to get the list of enums.
Borneman: TheRealGent: Can you do that?
Larroque: TheRealGent: You can certainly join with information_schema.
Duckwall: If that is not possible, I will have to explore other avenues or just add a bogus row into the table with that missing ‘status’
Clyatt: TheRealGent: Are you kidding? :
Clyatt: TheRealGent: INSERTing bogus rows instead of creating new table even temporarrily?
Behunin: Salle, but it won’t be temporary though.
Agent: TheRealGent: That’s the entire point of having a design which meets certain minimal requirements of normalization.
Clyatt: TheRealGent: You can CREATE TEMPORARY TABLE your_tbl .; in MySQL. Didn’t you know that?
Lucchetti: Salle, not to mention, is that how you do things at your company? People just pop up tables when they’d like to if it suits their purposes? This would have to be version controlled and deployed to all reporting instances.
Rhames: TheRealGent: You can use a derived table in your report, based on your java enum.
Pushaw: TheRealGent: Not even a temporary table. A derived table.
Consla: Salle, no, I did not know that actually. Is this only for the span of a connection/transaction?
Clyatt: TheRealGent: What I do in my company is to help people use MySQL : “My company” in the past was called MySQL AB by the way :