librelist archives

« back to archive

Suggestions for managing user settings

Suggestions for managing user settings

From:
Anthony Ford
Date:
2012-10-29 @ 05:54
Hey all,
I'm currently working on another internal flask app for work that's in
active development with a full user base. My current system has a MySQL
table that contains user records, and I'm facing an issue. There's a set of
features to be rolled out incrementally, but some of them introduce new
user setting that will require entries in that table. Is there a smarter
alternative for storing those variables other than adding new columns to
the table? I'd rather not have to take the app down to update the MySQL
schema for every update.

The user base is small (~20 people), so one idea I had was to create a
settings table where each record represented a setting with id, keyword,
and value columns. Using foreign keys I could then relate them to a user
via a one-to-many relationship. Any thoughts?

Thanks,
Anthony Ford,
KF5IBN,
Research Assistant,
Center for Advanced Radio Astronomy,
University of Texas at Brownsville
ford.anthonyj@gmail.com

Re: [flask] Suggestions for managing user settings

From:
Live Flex
Date:
2012-10-29 @ 08:50
Why do you have to take the app down to update the schema? With only 20
users, updates should be pretty much instantaneous.

Also, using key-value stores is usually not a good idea in mysql

On Mon, Oct 29, 2012 at 5:54 AM, Anthony Ford <ford.anthonyj@gmail.com>wrote:

> Hey all,
> I'm currently working on another internal flask app for work that's in
> active development with a full user base. My current system has a MySQL
> table that contains user records, and I'm facing an issue. There's a set of
> features to be rolled out incrementally, but some of them introduce new
> user setting that will require entries in that table. Is there a smarter
> alternative for storing those variables other than adding new columns to
> the table? I'd rather not have to take the app down to update the MySQL
> schema for every update.
>
> The user base is small (~20 people), so one idea I had was to create a
> settings table where each record represented a setting with id, keyword,
> and value columns. Using foreign keys I could then relate them to a user
> via a one-to-many relationship. Any thoughts?
>
> Thanks,
> Anthony Ford,
> KF5IBN,
> Research Assistant,
> Center for Advanced Radio Astronomy,
> University of Texas at Brownsville
> ford.anthonyj@gmail.com
>
>

Re: [flask] Suggestions for managing user settings

From:
Edd Robinson
Date:
2012-10-29 @ 09:42
On 29 Oct 2012, at 08:50, Live Flex wrote:

> Why do you have to take the app down to update the schema? With only 20 
users, updates should be pretty much instantaneous.
> 
> Also, using key-value stores is usually not a good idea in mysql

You 20-user logic also applies to bastardising MySQL into a key-value store.
 
Anthony - the long and short of it is that I don't think it really matters
whether you add feature-flags to the user table or have a separate 
settings table. Usually you will scale much faster in users than features,
so feature flags in a user table will make sense.

If you're changing your application, whatever's serving your application 
will have to reload etc - as Live Flex notes, changes to your schema will 
be faster than that process anyway!

Cheers,
Edd

http://eddrobinson.net



> On Mon, Oct 29, 2012 at 5:54 AM, Anthony Ford <ford.anthonyj@gmail.com> wrote:
> Hey all,
> I'm currently working on another internal flask app for work that's in 
active development with a full user base. My current system has a MySQL 
table that contains user records, and I'm facing an issue. There's a set 
of features to be rolled out incrementally, but some of them introduce new
user setting that will require entries in that table. Is there a smarter 
alternative for storing those variables other than adding new columns to 
the table? I'd rather not have to take the app down to update the MySQL 
schema for every update.
> 
> The user base is small (~20 people), so one idea I had was to create a 
settings table where each record represented a setting with id, keyword, 
and value columns. Using foreign keys I could then relate them to a user 
via a one-to-many relationship. Any thoughts?
> 
> Thanks,
> Anthony Ford,
> KF5IBN,
> Research Assistant,
> Center for Advanced Radio Astronomy,
> University of Texas at Brownsville
> ford.anthonyj@gmail.com
> 
> 

Re: [flask] Suggestions for managing user settings

From:
Anthony Plunkett
Date:
2012-10-31 @ 16:19
Assuming a settings value might be unique to a user, such as 'items to
display per page', why not go for three tables?

1. User (id, username, email, blah)
2. Setting(id, name, description, default_value)
And a many to many to join it:
3. User_setting(user_id, setting_id, value)

Then adding a new setting is simple an insert statement on the setting table.

Ant.

On 29 October 2012 05:42, Edd Robinson <me@eddrobinson.net> wrote:
>
> On 29 Oct 2012, at 08:50, Live Flex wrote:
>
>> Why do you have to take the app down to update the schema? With only 20
users, updates should be pretty much instantaneous.
>>
>> Also, using key-value stores is usually not a good idea in mysql
>
> You 20-user logic also applies to bastardising MySQL into a key-value store.
>
> Anthony - the long and short of it is that I don't think it really 
matters whether you add feature-flags to the user table or have a separate
settings table. Usually you will scale much faster in users than features,
so feature flags in a user table will make sense.
>
> If you're changing your application, whatever's serving your application
will have to reload etc - as Live Flex notes, changes to your schema will 
be faster than that process anyway!
>
> Cheers,
> Edd
>
> http://eddrobinson.net
>
>
>
>> On Mon, Oct 29, 2012 at 5:54 AM, Anthony Ford <ford.anthonyj@gmail.com> wrote:
>> Hey all,
>> I'm currently working on another internal flask app for work that's in 
active development with a full user base. My current system has a MySQL 
table that contains user records, and I'm facing an issue. There's a set 
of features to be rolled out incrementally, but some of them introduce new
user setting that will require entries in that table. Is there a smarter 
alternative for storing those variables other than adding new columns to 
the table? I'd rather not have to take the app down to update the MySQL 
schema for every update.
>>
>> The user base is small (~20 people), so one idea I had was to create a 
settings table where each record represented a setting with id, keyword, 
and value columns. Using foreign keys I could then relate them to a user 
via a one-to-many relationship. Any thoughts?
>>
>> Thanks,
>> Anthony Ford,
>> KF5IBN,
>> Research Assistant,
>> Center for Advanced Radio Astronomy,
>> University of Texas at Brownsville
>> ford.anthonyj@gmail.com
>>
>>
>

Re: [flask] Suggestions for managing user settings

From:
Mark Grey
Date:
2012-10-31 @ 16:51
Perhaps I'm misunderstanding, but if you are reworking the persistency from
the ground up and you are already leaning towards a key-to-value schema,
would it make sense to migrate to a key value based storage layer like
Mongo or Redis?

It may sounds like overengineering it, but I've done a few migrations with
similar use cases (internal administrative tools for gather analytics on
our webapps) and writing a script to batch my old user table into Mongo was
never much of a challenge, especially using the MySQLDB DictCursor to map
the fields as dictionaries at query time in the migration script.

Would this be feasible for the scope of your app? I think it would serve
you well if it could implemented, because the properties would become
schemaless and thereby new data could be added to individual users
gradually over time.

On Wed, Oct 31, 2012 at 12:19 PM, Anthony Plunkett <anthony@thefort.org>wrote:

> Assuming a settings value might be unique to a user, such as 'items to
> display per page', why not go for three tables?
>
> 1. User (id, username, email, blah)
> 2. Setting(id, name, description, default_value)
> And a many to many to join it:
> 3. User_setting(user_id, setting_id, value)
>
> Then adding a new setting is simple an insert statement on the setting
> table.
>
> Ant.
>
> On 29 October 2012 05:42, Edd Robinson <me@eddrobinson.net> wrote:
> >
> > On 29 Oct 2012, at 08:50, Live Flex wrote:
> >
> >> Why do you have to take the app down to update the schema? With only 20
> users, updates should be pretty much instantaneous.
> >>
> >> Also, using key-value stores is usually not a good idea in mysql
> >
> > You 20-user logic also applies to bastardising MySQL into a key-value
> store.
> >
> > Anthony - the long and short of it is that I don't think it really
> matters whether you add feature-flags to the user table or have a separate
> settings table. Usually you will scale much faster in users than features,
> so feature flags in a user table will make sense.
> >
> > If you're changing your application, whatever's serving your application
> will have to reload etc - as Live Flex notes, changes to your schema will
> be faster than that process anyway!
> >
> > Cheers,
> > Edd
> >
> > http://eddrobinson.net
> >
> >
> >
> >> On Mon, Oct 29, 2012 at 5:54 AM, Anthony Ford <ford.anthonyj@gmail.com>
> wrote:
> >> Hey all,
> >> I'm currently working on another internal flask app for work that's in
> active development with a full user base. My current system has a MySQL
> table that contains user records, and I'm facing an issue. There's a set of
> features to be rolled out incrementally, but some of them introduce new
> user setting that will require entries in that table. Is there a smarter
> alternative for storing those variables other than adding new columns to
> the table? I'd rather not have to take the app down to update the MySQL
> schema for every update.
> >>
> >> The user base is small (~20 people), so one idea I had was to create a
> settings table where each record represented a setting with id, keyword,
> and value columns. Using foreign keys I could then relate them to a user
> via a one-to-many relationship. Any thoughts?
> >>
> >> Thanks,
> >> Anthony Ford,
> >> KF5IBN,
> >> Research Assistant,
> >> Center for Advanced Radio Astronomy,
> >> University of Texas at Brownsville
> >> ford.anthonyj@gmail.com
> >>
> >>
> >
>

Re: [flask] Suggestions for managing user settings

From:
Lars Hansson
Date:
2012-11-06 @ 06:11
Another option is to store the settings in json format in a "settings"
field in the user table. This gives you a very flexible structure for
the settings and json is easy to work with both in Python and
Javascript.. The drawback is that you can't query on individual
settings anymore.

Cheers,
Lars

Re: [flask] Suggestions for managing user settings

From:
Anthony Ford
Date:
2012-11-06 @ 06:24
@Lars I think I like that idea. It gives me a way to field test a feature
before modifying the schema, and won't have to undo changes to the schema
if they get nixed.

Thanks everyone. I think I'm going to play around with ask the feedback.
You guys are right about being simple to update the schema. I think I'll
use Lars soln for quick changes that I'm testing, but otherwise just modify
the schema.
On Nov 6, 2012 12:11 AM, "Lars Hansson" <romabysen@gmail.com> wrote:

> Another option is to store the settings in json format in a "settings"
> field in the user table. This gives you a very flexible structure for
> the settings and json is easy to work with both in Python and
> Javascript.. The drawback is that you can't query on individual
> settings anymore.
>
> Cheers,
> Lars
>