mySQL DB Desgin

Question

In my application I have different categories that users can post their transactions as. Example: Food, Shopping, Movies, etc.. I want the user to be able to edit these categories and add/remove categories. What is the best way to store this information in a database.

1. A categories table with the category and corresponding user? Then I can query the uid and get all of their categories.

2. User table w/ a category field and seperate the categories by comma. Then I can query user and seperate their categories into an array w/ explode or something along those lines.

3. OR an idea I have not even thought of.

Answer 1

Don't separate entries by comma in a database field if you might use them as single values (e.g. just one category).

Read about database normalization. There are cases when you don't want to go all the way normalizing, but concatenating distinct values with comma doesn't belong into this category.

Don't fear long tables. Databases are usually optimized for this. If it gets slow, create an index or use another optimizing technique.

Option 1 is the answer.

Answer 2

Option 3

A Category Table CategoryID, Category_Name, etc...

A User Table UserID, User_Name, etc.

An intersection table USER_Category

UserID, CategoryID

Why?

If you have

UserName, CategoryName

and say

"Jim", "Food"
"Joe", "Food"
"Judy", "Beauty Products"

If you delete "Judy" because you are no longer interested in her transactions, you also lose the category "Beauty Products". This way categories and users are separate.

It is also easier to ask questions like "Who is spending most of their money on Food". If the user enters categories in, you may get "Food", "Groceries", "Dining Out", and a ton of different categories, maybe even misspellings like "fod", "fooooooooooooooooood", etc... So by having unified categories you can just select based on foodID in your queries.

Basically now the user can browse a list of categories and if they spot the category they are interest in from the list, they will create the entry UserID, CategoryID in the intersection table. This reduces the likelihood of misspellings. If the category isn't there, the user can add it. Then when the next user views the categories they will see the new entry so if they are interested they can just click it and reduce the chance of misspelling it.