default/override: An Elegant Schema for User Settings

blog-banner

JJ Maxwell


Share this Article

facebookXtwitterlinkedinmail

Don't miss an article.

Get most recent news and updates straight from Double Team!

Every app, even basic ones, needs to manage user settings. It always starts simple, right? "Does the user want the Weekly Email?" But then features grow, and suddenly you're dealing with tons of settings: themes, notifications, currencies, time zones, the list goes on. If you're building something complex, like Double – which is for investing – settings get really important.

We have to manage things like fraud settings, drift limits and wash sale prevention settings. Some of these we want to be user controlled, some should only be editable by an admin user. How you store and handle these settings really matters for your app's speed, how well it scales, and how easy it is to maintain. I've tried a bunch of ways to manage user settings, and some are way better than others. Let's look at common mistakes first, and then I'll show you a schema that we came up with recently that's been working really well for us.

Common Mistakes: What Not To Do

1. The Wide Table Trap

One common mistake to avoid is the wide table trap, where the initial thought for managing new settings is to simply keep adding columns. It may seem easy at first; for example, setting up daily emails might look as simple as adding a wants_daily_email boolean column to your users table with a quick ALTER TABLE command. However, this approach quickly leads to columns getting out of control.

Before long, you'll need columns for newsletters, app notifications, dark mode, time zones, and other features, causing your users table to become a massive, wide structure. Wide tables can negatively impact performance, as queries take longer because the database has to process huge rows, many of which are sparsely populated. Indexes become more complicated, and backups and restores take significantly longer.

Schema changes also become a pain point. Adding another setting involves another risky ALTER TABLE operation in a live application. Remembering the purpose of each column becomes a documentation nightmare, and changing or removing old settings becomes a major project.

Finally, because most users only modify a few settings, your users table becomes filled with wasted space in the form of numerous NULL values, further slowing down queries and wasting storage.

2. The Key-Value Mess

Another approach that may seem appealing after rejecting wide tables is the "grab bag" table, often implemented as a generic user_settings table with columns like user_id, setting_name, and setting_value.

At first glance, this key-value structure appears flexible, because it promises easy addition of new settings without altering the main table. However, bulk updates soon become a significant challenge. For example, adding a new setting to all users could require millions of insert operations, resulting in large, risky database transactions. This can lead to server instability and data inconsistencies, often necessitating complex error handling, retry mechanisms, and background jobs to ensure data integrity.

Data quality also becomes a concern because the setting_value field can accommodate any data type – text, numbers, booleans – making it difficult for the database to enforce consistency. Consequently, all data validation and type handling logic must be implemented in the application code, increasing complexity and the potential for bugs. Finally, user signup processes can become slower.

Setting default settings for new users may require inserting numerous rows into the user_settings table, adding latency to the signup flow and increasing the chances of failures during this critical operation.

3. The Schemaless Temptation

Another tempting approach, especially with the rise of NoSQL databases like MongoDB, is to store settings as JSON documents directly within each user's record. The schemaless nature of NoSQL databases initially appears advantageous, and using JSON for settings feels like a natural fit. Adding a new setting seems as simple as adding a new key to the JSON document. However, querying across users for specific settings becomes challenging.

While document databases excel at retrieving all data for a single user, asking questions about a particular setting across all users becomes slow and inefficient. For instance, answering "How many users use dark mode?" could require a full database scan, which does not scale efficiently.

Furthermore, the perceived benefit of being "schemaless" diminishes when data consistency is required. Ensuring that every user has a specific setting or validating data types becomes the application's responsibility, as the database provides no inherent mechanisms for these rules.

Finally, while NoSQL promises flexibility, settings migrations over time remain complex. Updating settings across numerous NoSQL documents often necessitates custom scripts and background jobs, mirroring the same challenges encountered with the "grab bag" SQL table approach in terms of complexity and potential issues.

A Better SQL Schema: default/override

After struggling with these problems, I've found a SQL schema that actually works well. It balances flexibility, performance, and keeps your data sane. It uses two key tables: default and override.

default Table: The Master List of Settings

Think of default as the single place where you define all possible settings in your app. It's like the blueprint for your settings system.

ColumnData TypeDescriptionExample
nameVARCHAR(255)Unique name for the setting (primary key).rebalance_cadence
valueTEXTThe default value everyone gets.'monthly'
value_typeENUM('boolean', 'string', 'integer', 'enum')What is the data type of this setting?'enum'
allowed_valuesJSON(Optional, for 'enum' type) List of valid choices as JSON.['daily', 'weekly', 'monthly'] (for rebalance cadence)
is_user_editableBOOLEANCan users change this setting themselves?TRUE (for rebalance cadence), FALSE (for internal flags)

override Table: User-Specific Changes

override only stores settings where a user has changed the default. It's kept lean and efficient.

ColumnData TypeDescriptionExample
user_idINTEGERLinks to the users table (part of primary key).12345
nameVARCHAR(255)Links to default.name (part of primary key).rebalance_cadence
valueTEXTThe user's chosen value for the setting.'weekly'
created_atTIMESTAMPWhen the user made this change.2023-10-27 10:00:00
updated_atTIMESTAMPWhen the user last updated this setting.2023-10-27 10:00:00

How to Use It

This system is designed to be simple and effective in practice. Adding a new setting is straightforward; you only need to insert a single row into the default table. Here, you define the setting's name, its default value, the data type, any valid options, and whether users are allowed to edit this setting. Crucially, this process requires no database schema alterations and avoids mass data modifications.

To retrieve a user's setting, the COALESCE function (or its equivalent in your ORM) efficiently handles the logic. It prioritizes checking the override table. If a user-specific setting exists there, that value is used. Otherwise, the function falls back to the default value defined in the default table.

SELECT COALESCE(so.value, sd.value) AS setting_value
FROM default sd
LEFT JOIN override so ON sd.name = so.name AND so.user_id = :user_id
WHERE sd.name = :setting_name;

Retrieving multiple settings for a user simultaneously remains efficient through the use of joins and filters. Furthermore, data validation is enforced at the SQL level. When a setting is modified, a new row is inserted into the override table. Before saving this override, validation can be performed based on the value_type and allowed_values columns defined in default, allowing the system to reject invalid setting changes proactively.

For user settings management, users can easily retrieve all their settings, encompassing both their personalized overrides and the system defaults, or filter specifically for settings they are permitted to modify. Users can then update their settings via the application's API, which incorporates built-in validation checks and persists valid changes to the override table.

Pros

Pros: This approach offers several advantages. It scales well, allowing for fast addition and retrieval of settings. Since only user modifications are stored, the database remains efficient. Data consistency is ensured by default which establishes data types and valid options, with database-level checks promoting reliability.

Settings management is simplified with default acting as a central list, eliminating ambiguity about setting definitions. The design inherently maintains data consistency, negating the need for supplementary background processes to synchronize settings.

The system is clean and organized, clearly separating defaults from user-specific changes, enhancing overall system clarity and usability. is_user_editable provides control over user modifications, enabling specification of settings users can adjust, which is beneficial for sophisticated applications.

Finally, it facilitates efficient bulk operations and the creation of user-facing settings interfaces, making it easy to retrieve numerous settings at once.

Cons

Cons: This system also presents some drawbacks. The primary disadvantage is its complexity compared to simpler methods like wide tables or key-value based approaches.

Additionally, gaining easy insight into historical settings is limited. As user settings rely on two tables, tracking a setting's value at a specific past time is not straightforward, requiring periodic snapshots of both tables for reconstruction, which is less than ideal.

Lastly, retrieving settings necessitates querying the application's backend to access the database (or use a cache). While slightly slower than direct column access from a users table, the benefits generally outweigh this minor performance difference. Database views can also be implemented to mitigate this speed concern in many scenarios.

In Short: Ditch Giant Tables, Use The Default-Override Method

For apps that need to be reliable and handle lots of users, especially complex ones like investing platforms, how you store settings really matters. Moving away from simple wide tables and using a structured schema like default/override has been a great move for us here at Double. It's cleaner, scales better, easier to maintain, and keeps our data in good shape.

What's your experience with handling user settings? Run into similar problems? Found different solutions?

Our Tools

See All

Fee Calculator

With zero AUM fees on Double, see how much savings a portfolio might accumulate compared to a traditional wealth advisor fee or ETF expense ratio.

empower-sm

0.89%

wealthfront-sm

0.25%

frec-sm

0.1%

1% Typical Advisor Fee

Tax Bracket Estimator

Estimate the taxes across federal, state, short term and long term for your situation.

JJ Maxwell


Share this Article

facebookXtwitterlinkedinmail

Our Tools

See All

Fee Calculator

With zero AUM fees on Double, see how much savings a portfolio might accumulate compared to a traditional wealth advisor fee or ETF expense ratio.

empower-sm

0.89%

wealthfront-sm

0.25%

frec-sm

0.1%

1% Typical Advisor Fee

Tax Bracket Estimator

Estimate the taxes across federal, state, short term and long term for your situation.

Don't miss an article.

Get most recent news and updates straight from Double Team!

Double is a registered investment adviser with the US Securities and Exchange Commission (SEC). While such registration does not imply a certain level of skill, it does require us to follow federal regulations that protect you, the investor. By law, we must provide investment advice that is in the best interest of our client. Please refer to Double's Form CRS for important additional information.

The publicly available portions of the Platform (i.e., the sections of the Platform that are available to individuals who are not party to a Client Agreement - including double.finance) are provided for educational purposes only and are not intended to provide legal, tax, or financial planning advice. To the extent that any of the content published on publicly available portions of the Platform may be deemed to be investment advice, such information is impersonal and not tailored to the investment needs of any specific person. Nothing on the publicly available portions of the Platform should be construed as a solicitation or offer, or recommendation, to buy or sell any security. All charts, figures, and graphs on the publicly available websites are for illustrative purposes only. Before investing, you should consider whether any investment, investment strategy, security, other asset, or related transaction is appropriate for you based on your personal investment objectives, financial circumstances, and risk tolerance. You are also encouraged to consult your legal, tax, or investment professional regarding your specific situation. Registration does not imply a certain level of skill or training.

Investing involves risk. The value of your investment will fluctuate, and you may gain or lose money.

The contents of the Platform may contain forward-looking statements that are based on management's beliefs, assumptions, current expectations, estimates, and projections about the financial industry, the economy, or Global Predictions itself. Forward-looking statements are not guarantees of the underlying expected actions or future performance and future results may differ significantly from those anticipated by the forward-looking statements. Therefore, actual results and outcomes may materially differ from what may be expressed or forecasted in such forward-looking statements.

¹ Data as of July 2024.

² Availability of tax loss harvesting depends on portfolio diversity.

³ As of November 8, 2024. The optional Cash Sweep program takes the cash sitting in your brokerage account and moves it to an FDIC-insured interest-earning deposit account. The current yield is 1.0%, rates subject to change at any time. The cash sweep program is made available in coordination with Apex Clearing Corporation. Please read the Important Disclosures for more information.

⁴ This analysis calculator tool is for illustrative purposes only and is not financial advice. We do not guarantee the accuracy of the results or their relevance to your particular circumstances. This calculator uses hypothetical historical data and does not take into account the effect of taxes on a taxable account. Hypothetical historical data is no guarantee of future performance and this calculator is not intended to predict actual performance. The input of different time periods, amounts, and fees will vary calculator results. Average ETF fee is based on the AUM weighted average of US ETFs as of September 2024, see more information. Note that you can purchase ETFs through Double, which may incur additional fees.

Full pricing details along with an FAQ can be found here

Data displayed in charts and graphics above are for illustrative purposes only and do not reflect actual investment results and are not a guarantee of future results.

By using double.finance, you accept our and . Double is only available to US residents.

See Double Finance Form ADV Part IIA for additional information. Double does not guarantee that the results of its advice, recommendations, or the objectives of its direct index or cash management strategies will be achieved. We make no assurance that the investment process will consistently lead to successful investing. Before you invest, you should carefully review and consider your investment objectives as well as the risks, charges, and expenses of the underlying securities. There is at least a $1,000 account minimum required to participate in Double's strategies, although some strategies may have higher minimums.

Double does not provide tax, or legal advice. Double's website, brokerage, and advisory services are not intended for persons of any jurisdiction where Double is not authorized to do business. Tax savings will vary from client to client due to many factors including market conditions, tax characteristics of securities, client-imposed investment restrictions, client tax rate or status, and changes in tax regulations.