Database Structure Help...thanks

[bostonmacosx]

I'm going to have a database where people will have "categories" they can be affiliated with. So like "parent" "uncle" "cousin".

They would then be searchable by these categories.
Because categories will be able to be added and removed making each category a column in the table is impractical I believe.

I'm thinking that the categories in one column and the data would be dlimited so as to be text searchable.

ie User 1 has Dad:Uncle:Cousin in their data as categories.

Any ideas on the correct way to set this up and then how to search it efficiently.

Thanks
Robert

[bretticus]

Sounds like you need a users table accompanied by two other tables. One would be categories of course. However, you need a many-to-many relationship. Another table can facilitate this by storing a foreign key to a userid and a foreign key to a category id (since User 1 can be a Dad, Uncle, and Cousin simultaneously.) Graphically, this looks like:

Users UserCategories Categories

---

PK Userid <- FK Userid | FK CategoryID -> PK CategoryID

As for searching, simply join the three tables together to search for all users under a specific category. If you use a dropdown (select) menu for the categories, you can use the associated CategoryIDs and just join Users and UserCategories for your query.