Re: multiple categories

Andrew Ballard wrote: > On Mon, Mar 2, 2009 at 11:50 AM, PJ <af.gourmet@videotron.ca> 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@ptahhotep.com >> Â 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 > > Thanks, Andrew. I rather suspected that was the case and am now much relieved. Now for the horrible task of working out the category insertion... etc. etc. -- Phil Jourdan --- pj@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com