During my application I've different groups that customers can publish their transactions as. Example: Food, Shopping, Movies, etc.. I would like the consumer to have the ability to edit these groups and add/remove groups. What's the easiest method to store these details inside a database.

  1. A groups table using the category and corresponding user? I Quickly can query the uid and obtain all their groups.

  2. User table w/ a category area and seperate the groups by comma. I Quickly can query user and seperate their groups into an assortment w/ explode or something like that along individuals lines.

  3. Or perhaps an idea I haven't even considered.

Option 3

A Category Table CategoryID, Category_Title, etc...

A Person Table UserID, User_Title, etc.

An intersection table USER_Category

UserID, CategoryID

Why?

For those who have

UserName, CategoryName

and say

"Jim", "Food"
"Joe", "Food"
"Judy", "Beauty Items"

Should you remove "Judy" since you are no more thinking about her transactions, additionally you lose the course "Beauty Items". By doing this groups and customers are separate.

It's also simpler to request questions like "Who's investing the majority of their cash on Food". When the user makes its way into groups in, you might get "Food", "Groceries", "Eating OutInch, and a lot of different groups, possibly even misspellings like "fod", "fooooooooooooooooood", etc... So by getting unified groups you can easily choose according to foodID inside your queries.

Essentially the user can browse a listing of groups and when they place the course they're curiosity about in the list, they'll produce the entry UserID, CategoryID within the intersection table. This reduces the probability of misspellings. When the category is not there, the consumer can also add it. When the following user sights the groups they'll begin to see the new entry so if they're interested they are able to simply click it and lower the risk of misspelling it.

Don't separate records by comma inside a database area should you would use them as single values (e.g. only one category).

Find out about database normalization. You will find times when you won't want to go completely normalizing, but concatenating distinct values with comma does not belong into this category.

Don't fear lengthy tables. Databases are often enhanced with this. Whether it will get slow, create a catalog or use another optimizing technique.

Option 1 may be the answer.