ASP.NET Core应用程序自定义设置存储方案咨询

Hey there! Let’s tackle this inventory settings storage problem you’re facing—single-row tables for configs can feel clunky and inefficient, especially with frequent queries. Here are some practical alternatives I’ve used in real-world projects that solve this pain point:

Instead of a dedicated single-row table, create a generic key-value table that can hold all your app’s settings (not just inventory ones). This way, you avoid spinning up new tables for every config type, and queries become far more flexible.

Example table structure (SQL):

CREATE TABLE app_settings (
    id SERIAL PRIMARY KEY,
    setting_key VARCHAR(255) UNIQUE NOT NULL,
    setting_value TEXT NOT NULL,
    setting_type VARCHAR(50) DEFAULT 'string', -- Helps with type casting (e.g., 'int', 'bool', 'json')
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

To store your InventorySettings, insert rows like:

INSERT INTO app_settings (setting_key, setting_value, setting_type)
VALUES 
('inventory.max_stock', '1000', 'int'),
('inventory.auto_restock', 'true', 'bool'),
('inventory.alert_thresholds', '[50, 20]', 'json');

Fetch all inventory-related settings in one go:

SELECT setting_key, setting_value, setting_type 
FROM app_settings 
WHERE setting_key LIKE 'inventory.%';

Pros: Extensible (add new settings without altering the table), eliminates redundant tables, easy to batch queries.

If your InventorySettings have a complex, nested structure, use a JSON or JSONB (PostgreSQL) column in a shared configuration table. This lets you store the entire inventory config as a single JSON object, while keeping other app configs separate.

Example for PostgreSQL:

CREATE TABLE app_configs (
    id SERIAL PRIMARY KEY,
    config_namespace VARCHAR(255) UNIQUE NOT NULL, -- e.g., 'inventory', 'checkout'
    config_data JSONB NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert your inventory settings:

INSERT INTO app_configs (config_namespace, config_data)
VALUES ('inventory', '{
    "max_stock": 1000,
    "auto_restock": true,
    "alert_thresholds": {"low": 20, "medium": 50},
    "supplier_contact": {"name": "ABC Corp", "email": "supplier@abccorp.com"}
}');

Fetch the full config or specific fields:

-- Get entire inventory config
SELECT config_data FROM app_configs WHERE config_namespace = 'inventory';

-- Get a specific setting
SELECT config_data->>'max_stock' AS max_stock FROM app_configs WHERE config_namespace = 'inventory';

Pros: Preserves the structure of your settings, easy to update the entire config in one query, works great for nested data.

If you don’t want to overhaul your database structure right away, add a caching layer to cut down on frequent database queries. Tools like Redis or even an in-memory cache (like a singleton in your app) can store the single-row InventorySettings after the first fetch.

Workflow:

1. When your app starts, fetch the settings from the database and store them in the cache.
2. For subsequent requests, pull settings directly from the cache.
3. When updating settings, write new values to both the database and cache to keep them in sync.

This immediately reduces unnecessary database hits without changing your existing table setup.

If your InventorySettings rarely change (e.g., default values that don’t need runtime updates), store them in environment variables (using a .env file, for example). Your app can load these on startup, eliminating database queries entirely for these configs.

Note: This only works for settings that don’t need modification without restarting your app. For dynamic, updatable settings, stick to database-based options above.

Final Recommendation #

• Go with the key-value table if you have lots of flat, independent settings you might expand over time.
• Use the JSONB column if your InventorySettings have a nested or complex structure you want to keep intact.
• Use caching as a quick fix if you need better performance without changing your current setup.

内容的提问来源于stack exchange，提问作者Jackal