* "al butler" <[EMAIL PROTECTED]> > I have the MySQL book and I am trying to come to terms on how > to setup a column that is based on another one. > > Let me try and explain. > > Each row has a string (unique) and indexed. > This string is catergorized by anothe column defined as a SET , > let's call > it 1,2,3,4,5,6. > From my readings this seems to be effiecient than having it > classified as a > VARCHAR and is > also readable. It's treated as an int and can be indexed faster and more > effecient.
Correct. > But for item 6 in the SET, you can have up to 20+ categories. > This sub-category I have defined as another SET and it's named something > like > sub6. Now the SET can have other subcategories, so right now I would add > another column > for each SET that needs a sub-category. Kind of ugly. Yes... :) > Let's recap: > col1 "string" (VARCHAR) 64 > col2 "category" (SET) 1,2,3,4,5,6 > col3 "subcategory of col2 6) SET A,B,C,D,E,F,G,H > > A set can have 64 items. I don't forsee col2 to ever reach this. > I can forsee col3 possibly having 64 sub-categories of col2. > > My question. > Is this the best I can do for a sub-category? Any suggestions? One word: normalize Add an INT primary key, (keep the UNIQUE constraint on col1) and add a second table for col3 and potential future sub categories. This table should also have an INT primary key. Then add a third table to connect the two, with two INT fields, and a compound primary key, containing both INT fields. Also, add an index for the second field of the primary key[1]. These two fields are foreign keys, and 'points to' the keys of your main data table and the category table. This way, you will keep up the speed of your queries, but, just as important, you will have a clean design wich scales well, without the need for repeatedly doing changes to the db schema. [1] If the primary key is (data_id,category_id), it will be very slow to do a search for all occurrences of data items for one category without an extra index on category_id, and if it is (category_id,data_id) it will be slow to list all categories of an data item without an index on data_id. If you don't need one or the other, you can drop this index, just make sure you put the right id first in the primary key. -- Roger --------------------------------------------------------------------- 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
