current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Premature optimization is the root of all evil...but... I am allowing users to input data within categories as in favorite players, favorite teams etc. They can then use these choices to filter results. I let them input lists separated by commas so after exploding the data I have it in an array. So how to store.

Method 1: I could create a table of users, one row per user, with the categories, as in players, teams as fields and save the choices of each users as an array in the respective field. (userid would link to basic users table.)

Method 2. Or I could create separate tables for each thing, players, teams, etc, and have a fixed number of fields say 10, break up the array into each individual value, store and place it in its own field. (Already have this code working.) (Again userid is primary key.)

The advantage of Method 1 is it's a bit simpler, one table, no limit on number of choices.

Method 2 seems a bit more robust. The data is more visible and possibly easier to get and retrieve--although maybe not.

Does anyone have experience with this sort of thing and could recommend one over another?

Thanks for any recommendations, suggestions!

share|improve this question
    
Ok. Method 1 is out. On method 2, if I understand, your method is to add new record for each category linked to userid so there is no limit to categories per user. My method was to have one record per user but have say ten fields at top of the users categories--some to be blank if users do not fill them in. Since I also have, in effect, sub topics, my method 2 also requires creating separate table for each topic. Is my method flawed or would you recommend yours over one record/user-especially to handle topic ie team and sub-stopic, name of team. Thanks –  user1260310 Mar 31 '12 at 16:54
    
Multi value fields and repeating field sets are equally evil. Querying against data stored in these types of structures is very inefficient and cannot be optimised. You should use association tables for storing these relationships. This first normal form article gives a brief explanation of why you should avoid these types of structures. –  nnichols Mar 31 '12 at 18:30
    
@user1260310 I'm sorry I misread (or failed to completely absorb) your "method 2" initially, and I retract that part of the comment :) Using the method described in my other answer, you can create association tables for each type of thing (users to players, users to teams, etc). e.g. For every player a user favorites, a row is added to the user->player table holding the userid and the playerid (or name). –  Michael Berkowski Mar 31 '12 at 22:25
    
See my answer yesterday on a similar question. –  Michael Berkowski Mar 31 '12 at 22:26
    
Thanks! One last question. Should there be one table of favorites with two fields for team and players or two tables, one favorite teams, one favorite players linked by userid? Latter would have fewest empty cells... –  user1260310 Apr 1 '12 at 23:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.