How would you handle this?

[Phrank]

I have a database of books. The books can appear in one or more categories like 'Christian' or 'Christmas", etc. There are 50 categories and they change from time to time.

The previous programmer put 50 1 char fields in the database and everytime you change or add a new category you have to also do that in the books database. He has a 50 check boxes on the book form.

I would think you could use an array with a select statement and then have just 1 field in the books database with a list of the categories that the user has selected instead of 50 fields. That way all would be dynamic. The problem is I have tested with a select statement using the 'multiple' option and having the name as an array and it works great.

I am not sure how you put this array into 1 field on the books database called say categories, char, 254.

I am also not sure how to show which categories were selected for the select statement when the user brings a book up to edit it.

Is there a neat way to save an array in a database in 1 field and get it back again? Is there a way then to fix-up the select statement when the user comes back to edit the book so that the categories they selected last time show up in the select statement drop down?

What are your thoughts? How would you handle it?
:glare:

[NogDog]

I would have a separate table for the book-to-category relationship, so you would have 3 tables: books, categories, book_to_category. The book_to_category table rows would only need, at a minimum, two columns: the book primary key ID and the category primary key ID. (This is where the "relational" in "relational database" comes into play.)

[Phrank]

Not bad. Makes alot of sense. Would work. Thank you very much.