在数组中创建新表或存储

Question

I'll try to simplify this as much as possible I'm in the process of developing an application which serves a series of resource(X) each from a category(C) to its users(U). Users will select a Category, then be served the resources from that category. They can choose 6-7 categories(out of 15-16), and once they have selected the category they will be able to select individual resources to be served or not within the category; there will be upwards of 10000 resources of which the user can select at most 40-50. I think storing the category choices as an array called preferences should be fine(correct me if I'm wrong) since it will only be a 20 something character string). However, I'm questioning whether I should do the same with resources since it will will be upwards of 200 character string. My alternative is to create an user-resource table, that lists treats each pairing like a transaction. So user(1) might have 30 rows each associating itself with an individual resources.

So each user has:

• ID
• Name
• Preferences(array of 3 letter abbreviations for categories(C) of resources, max of 6-7 so I don't think putting it in a comma delineated string will be an issue)
• Resources:Should I include another array here?

Each Category(C) has just:

• Id
• name

Each Resource (R) has:

• ID*
• Name
• Category

<b>Or create an User-Resource Table here:</b>
<ul>
<li>Id*</li>
<li>Resource Id:</li>
<li>User Id:</li>
</ul>

Answer 1

If you store your resources in a string you'll have trouble querying them afterwards. There's no problem in storing 200 characters long strings, but inserting or deleting resources may be troublesome, as well as querying (how do you count the number of users that chose a particular resource for instance ?).

Create a Users-Resources table to store this. 30 rows per user is not a problem, that's what databases are about. Just take care of creating the right foreign keys and indexes.

In fact, you should also do the same for preferences.

Read about database normalization (especially the third normal form).