With something like this, the big question is how are you going to use the data once stored?
If you're just using it as preferences within the app, where you could potentially have say a serialized object or array that contains the needed preferences which is loaded into the session when the user logs in, then serializing would be the way to go. But the question is, are you going to need to query against these preferences for any other reason? Would you potentially have reports that need to join on users that have a given preference set? If so, then you'd be more likely to need a field for each option, or a preference-specific row to be able to query that. If you might only need to query on some options and not on others, would it make sense for your project to split them up? So you would store many of the preferences in a serialized object, but then specific items you need to query on for reports, etc. would be stored in a different table or simply in another column on the same record as the serialized object, etc. Seems to me if you truly could have 100+ preferences, you'd probably have to do a mixture of both for efficiency's sake. I'm assuming though that these aren't all necessarily user-selectable preferences in the sense of 100 checkboxes in a settings screen somewhere, but that you probably have quite a few toggles that might get set depending on factors in your application, but that still seems like a ton of variables to be dealing with. I'd start by considering how likely it is that you will need to join on any given option and work from there. On Thu, Jul 28, 2011 at 9:18 PM, Jonathan Duncan < [email protected]> wrote: > DBers, > > I am creating a preferences/settings table for a project. Assuming this > project will eventually have millions of users and possibly 50-100 > preferences per user, what is the best practice for storing this in an > RDBMS? > > I have seen that WordPress does a combination of more rows and serialized > data. > > I have seen some database tables that have 100+ fields, one for each > option. > > Then there is the table with a handful of fields and then a row of data for > each option. If there are 100 users and 100 options that means and easy > 10,000 rows. > > Then again, serializing the data makes the database less cumbersome, but > puts the parsing work into the app. > > Another possibility is grouping the options into multiple tables. > > Thoughts? > > Thank you, > Jonathan Duncan > > > > > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net > _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
