如何实现多用户共用单个warehouse数据库表？

Hey there! This is a classic multi-tenant use case, and the solution is straightforward while keeping your database structure clean. Let’s walk through how to set this up so each user (like ABC and DEF) has their own isolated "slice" of the warehouse table.

Core Idea: Add a User Association Field #

The key is to link every product entry in warehouse to a specific user in your users table. This way, you can easily filter which products belong to which user without needing separate tables.

Step 1: Define Your Table Structures #

First, confirm your users table is set up (you mentioned it already has ABC and DEF), then modify or create the warehouse table with a foreign key pointing to users.id.

-- Users table (you likely already have this)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    -- Add other user fields like email, password_hash, etc. as needed
);

-- Warehouse table with user association
CREATE TABLE warehouse (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    price DECIMAL(10,2),
    -- Critical field: links each product to its owning user
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

The ON DELETE CASCADE ensures that if a user is removed from the users table, all their associated warehouse products are deleted too—adjust this to ON DELETE SET NULL if you want to retain products without an owner instead.

Step 2: Insert Sample Data #

Let’s add your existing users and tie sample products to each:

-- Insert users ABC and DEF
INSERT INTO users (username) VALUES ('ABC'), ('DEF');

-- Add products exclusive to ABC
INSERT INTO warehouse (product_name, quantity, price, user_id)
VALUES ('Laptop', 5, 999.99, 1), ('Wireless Mouse', 20, 19.99, 1);

-- Add products exclusive to DEF
INSERT INTO warehouse (product_name, quantity, price, user_id)
VALUES ('Mechanical Keyboard', 15, 49.99, 2), ('27" Monitor', 3, 299.99, 2);

Step 3: Query Data for Specific Users #

When a user logs in, filter their products using the user_id to ensure they only see their own inventory:

-- Fetch all products for ABC (user_id = 1)
SELECT * FROM warehouse WHERE user_id = 1;

-- Fetch all products for DEF (user_id = 2)
SELECT * FROM warehouse WHERE user_id = 2;

Pro Tips for Scalability & Security #

• Add an Index: Speed up user-specific queries by indexing the user_id field:

  CREATE INDEX idx_warehouse_user_id ON warehouse(user_id);
  

• Enforce Access Control: In your application code, never let users query the full warehouse table directly. Always include the user_id filter in every warehouse-related query to prevent cross-user data leaks.
• Advanced Isolation (If Needed): For stricter separation (like compliance requirements), you could explore schema-level isolation per user. But for most cases, the above approach is simpler and more efficient.

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