Altering Database Tables on the Fly, why is this a Bad Idea?

Question

I recently saw a comment on StackOverflow that basically said it is a bad idea to alter tables on the fly.

In my case I have a json file that is storing some information about some "products". One of those pieces of information tells other programs which column in a data table is related to this product. This id is not user defined.

I have some php functions that can add/delete products from the json file but during these functions, a column in a table either has to be added or removed.

$sql = 'ALTER TABLE products ADD p'.$id.' VARCHAR(11)';
$stmt = $mysql->prepare($sql);
$execute = $stmt->execute();
if($execute!==false){
    $build = ["name"=>$product_name, "release"=>$release_date, "details"=>$details, "id"=>$id];
    array_push($products["products"],$build);
    $products = json_encode($products,JSON_PRETTY_PRINT);
    $file = fopen("./crm.json","w+");
    fwrite($file,$products);
    fclose($file);  
}else{
    //error message
}   

Could someone explain why this may be a bad idea?

More Information:

To explain a bit more about my specific case the json file stores all of my product info. The id of each product entry is used to indicate which column I need to be looking at in a table to see if a user is either using this product or not. Other than that, the id is used to know which column should be dropped if a product is removed. Below is a json sample

{
    "location": "Test Location",
    "products": [
        {
            "name": "SAMPLE PRODUCT",
            "release": "2019-12-04",
            "details": "RANDOM PRODUCT DETAILS",
            "id": 1
        },
        {
            "name": "SAMPLE PRODUCT",
            "release": "2019-12-04",
            "details": "RANDOM PRODUCT DETAILS",
            "id": 2
        },
        {
            "name": "SAMPLE PRODUCT",
            "release": "2019-12-04",
            "details": "RANDOM PRODUCT DETAILS",
            "id": 3
        }
    ]
}

Answer 1

It depends. It is particular a bad idea in case the database table is used for sharing data between different processes or programs, and if one modifies the structure of the schema whilst another one accesses it, this will lead to collisions.

However, under certain, restricted circumstances, this can be a possible solution. But

You have to know precisely what you are doing !!!

• The table must be used exclusively by the process which alters the table, at least at the time when the altering happens. No other process must try to use it during modification time.

• Processes which will use the altered table will have to work with it in the same dynamic fashion as the creating process. This will probably require to find out which columns the altered table has or not. Note there is no standard vendor independent way of getting the column names of a certain table, one usually has to query some DBMS specific system tables to determine the columns at run time. This usually won't work together with things like ORMs, which often rely on a fixed db schema.

• The process needs sufficient access rights to alter the table. For smaller systems, this can be tolerable, for larger ones it is often a good idea not to give them these access rights to prevent data loss in case of a bug.

• Whenever a column to a table is added, you may also have to add or rebuild related indexes and maybe foreign key constraints. This can have a severe impact on run time performance.

A typical use case where such an approach may be feasible could be an ETL process, which imports the content of a JSON file into an intermediate temporary table for its own, exclusive usage, before it is loaded into the "regular" database with a fixed schema.

So check if that is your kind of case here and if using dynamically generated columns brings you really a benefit, or if you cannot model the schema differently in a way it stays fixed for your JSON import.

Let me add a note to the example of your question: a standard modeling for what you described would look like this:

• one Product table with primary key productId

• one Customer table with primary key customerId

• one link table UsedBy with columns for foreign keys productId and customerId (and proper indexes), containing one record for every combination where a customer uses a specific product.

This does not require to add or remove any column at running time - it is the standard way of modeling n:m relationships between entities like customers and products. Relational databases are pretty much designed for handling relations that way, not by adding columns dynamically.

Answer 2

It's a bad idea, because the database should have a fixed, versioned scheme.
Thus you can keep track on it with certain migrations of the database.

If the json is representing the primary scheme for incoming data, you should provide a migration of your database accordingly.

---

Also it seems you're storing additional information about the products in a column name. This should be rather normalized, and stored in an extra table that keeps the available product IDs being referenced by a foreign key column in the products table.

Answer 3

I see what you are doing. Without a greater understanding as to why you chose this design I cannot say whether or not it is “a good idea”.

That said, you mentioned you are just learning about this stuff. And the only use case I can synthesize for such a design is if your system needs to optimize for the single query, “how many customers use this product?”. I’m am not a database expert (so maybe someone can correct me), but I believe your design may be faster than storing your data using a traditional join table across customer_id and product_id because you can create an index per (customer, product) combination. Depending on some specifics regarding data types, I think it may be more space-efficient as well.

With the above in mind, I’m suspicious to say the least! You are trading off quite a bit of speed and convenience when issuing certain common kinds of queries (e.g. “what is the average product price for each customer?”) using your design than an alternative, more normalized approach.

Instead of using two tables (‘customer’ and ‘product’) you can use three tables (‘customer’, ‘product’, and ‘product_usage’) to model customer information, product information, and a record of “which customer uses which products” respectively. ‘product_usage’ simply needs two columns: customer_id and product_id (maybe a third if you want to maintain a surrogate key).

The above design optimizes for flexibility. This may be preferred.