I'm building a system for a nonprofit using two tables (actually a lot more than that, but I'll keep it simple here). One table we'll call "items" and the other we'll call "categories".
Categories are basically types of programs the org offers
- Health Programs
- Education Programs
- Job Training Programs
etc.
Items are the specific programs
- Food Bank
- Health Clinic
- Afterschool Tutoring
etc.
In the item table there are probably 15 fields, containing info like the title, a summary, and a detailed description (in two different languages), info about who to contact if you're interested. And a field for which category the item belongs to.
In some cases though, an item belongs to several categories. I've been dealing with this by listing all the categories in the "category" field, comma separated, and I've made the field a varchar instead of a numerical field (which it could have been if there was just a single category for each item). I can then extract the categories by exploding on the commas. No big deal.
Seems this is sloppy database practice though. My understanding is - one field, one piece of information.
So what's the best way to achieve this? I could insert several entries for each item - one for each category it's associated with - but that violates another 'best practice', which is having each piece of info in the database only once (except for foreign keys which are used to relate tables to each other).
Any suggestions?
Thanks
