On Nov 16, 2006, at 2:16 AM, James Masters wrote: > I use ENUM sometimes. It seemed like a straightforward thing to me > (although I think either MySQL or my interface to it seems to have > trouble > with an enum option of the empty string). Leaving aside > portability, what > bad design patterns does it promote?
Its not really normalized. Enum should be a 1:1 table relationship (or constraint, but in most db's I've seen a 1:1 is a better option). Set should be 1:many . most small systems i've seen have used at least 3 groups of identical set / enum fields across related tables strongly suggesting that things were not normalized right. you can mimic the enum in a constraint ( and i heard that its possible to do some crazy stuff with constaint + trigger / function to store it as bits ) but i think its a pretty messy solution. On Nov 15, 2006, at 11:26 PM, Ask Bjørn Hansen wrote: > Sometimes you can make a better bikeshed when you use all the tools > available. Sometime a SET column is one of those helpful tools. sure it can be - if you know what you're doing. but a jackhammer in a skilled construction worker can tear a precision hole -- and in the wrong hands, it'll ruin an entire street. a large number of mysql developers only know sql in terms of mysql (it is the most popular db) , and pull a lot of design patterns and ideas off of their docs and some poorly written textbooks. if you ever work in a shop that gets stuck with a lot of cleanup jobs and database migration ( which i unfortunately have ), and you pick up client with a bunch of mysql dbs - it can be hell. > Are you proposing to take out the Informix SET support too? no. i'm proposing that Rose require a user to set a variable acknowledging that the schema it is going to create is neither standardized nor portable -- because its neither. i didn't know that there was infomix set support -- i think that Rose should have a 'acknowledgement' flag for that as well. maybe just toss it in the register_db declarations , or require the driver to be 'xx-nonstandard' i don't mean for it to be user-hostile -- but a lot of people don't think about the ramifications of non portability. whether you have to switch dbs for your own reasons, or your client has an IT policy of Oracle / SQL Sever only , five seconds of work before you make the dbs can save you 5 days of work a few months later. > Have you ever actually ported an application from one database system > to another? Your comments makes it sound like you just drank the > kool-aid... i used to work for a design agency that grabbed a bunch of fortune 500 clients, and would take over all the web/marketing work for their sub-brands/divisions. maybe 1/3 of our work was 'new' sites -- but the bulk was migrating / updating / and redeploying existing sites that random agencies would create. at that agency I ported about 20 apps from mysql to postgres, 10 from mysql to oracle, 5 from ms sql server to postgres and 7 from mysql to ms' sql server. the 'from mysql' migrations were all hampered by far too much SET and ENUM usage , which made the porting a nightmare. since then, i ported two large schemas (250+ tables each ) that I admittedly made myself before having issues with SET/ENUM from mysql to postgres . i should have known better -- getting those 2 migrated took about a week each. when you port that stuff, you're not just updating the field, but you need to also handle all the foreign key chances and then audit & update all the views / sql queries . then do integrity testing. its not fun. in fact, it completely sucks. ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
