On Mon, Mar 2, 2009 at 11:50 AM, PJ <af.gourmet@xxxxxxxxxxxx> wrote: > I have a list of some 60 categories for a book database and am wondering > what would be the best approach for inserting and selecting the data. > My choices are to > (1.)simply use one field in a books table and enter the categories for > each book separated by a comma (or whatever) as a text field and then > use full text search to select the category(ies) for each book (out of > the 60+ categories any one book might have maybe up to 3 or 4 categories. > This seems like it might be cumbersome for selecting as there could be a > rather lengthy search (if there would be 10,000 books or more). > Or > (2.) use an intersecting table with foreign keys linking the categories > to the books. > > Now, inserting the categories for method 1 is simply a matter of > entering the categories in the field as text. But entering the > categories for method 2 would be a rather lengthy and complicated > conditional script using a dropdown select table to link the categories > and the book through the 3rd table. > > Or is there some other magical solution? I hope my explanation is not > too confusing... :-) > TIA > > > -- > > Phil Jourdan --- pj@xxxxxxxxxxxxx > http://www.ptahhotep.com > http://www.chiccantine.com I always go with #2 for this type of relationship. It may take a little extra processing to insert, but you'll reap the benefits when it comes to selecting items. Using an index on the relation table will usually be much faster than substring matching on a delimited value stored in a text field, it is easily expandable without the possibility of later having to enlarge the text field to accommodate longer and more subjects, and it avoids logic errors that result in invalid results when differentiating between subjects like "Men's Health" and "Women's Health" within the list. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
