Hi Michael, On Fri, 2002-05-10 at 02:27, Michael Davis wrote: > I'm trying to allow an attribute to hold multiple value. I know you can use > varray in oracle but I cant find anything in mysql. I found SET but you > must set the possible choices from the beginning. This is not good for what > I'm doing because these choices will change. The only other thing that I > could think of was to just encode all the choices in string and then decode > them when I retrieve them. Any ideas.
Stuffing multiple values into a single column is contrary to relational principles. Yes it can be useful sometimes, but look at the problems when searching in SET type columns.... The relational workaround for this is using a separate table, containing id,attribute, PRIMARY KEY (id,attribute). The id is the (autoincrement) id from the main table, and the attribute is whatever property you wanted to link to this id. Because the primary key combines the two fields, you can have multiple attributes per id, while still preventing duplicate rows. With this design you can easily add/remove attributes, check whether an id has a certain attribute, find all ids that have a certain attribute, or find all attributes for a given id. It may appear to be more complicated in design, but since the RDBMS takes care of all the legwork, it's actually very elegant and easy to use in queries. By the way, stuff like this (and much more) is taught in MySQL training courses, see http://www.mysql.com/training/ for details. Regards, Arjen. -- MySQL Training in Australia: 9-13 Sep 2002, https://order.mysql.com/?marl __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
