3

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
        }
    ]
}
  • This looks confused. I have no idea why you cannot simply have a fixed integer column "id" in your products table. Moreover, if you have different product records, you cannot have "one column for one product record" and a different column for another product record at the same time. The code example looks like you are trying to add columns named 1, 2 and 3 to the product tables which will actually not work since columns names cannot be just a number. Please clarify. – Doc Brown Dec 4 '19 at 20:47
  • My products table carries different information from the product information in the json file. The products table actually stores an identifier for a customer. When im adding a product I add a new column to my products table which simply shows if that customer is using that product or not. The id used for the column name allows me to relate a product in the json file to a customers status with that product. I am still new to all of this but I appreciate you attempting to understand – FamousAv8er Dec 4 '19 at 20:51
  • So if you have 5000 customers, you may add 5000 columns to your product tables? That does not sound feasible. And from your example, I have no idea how the columns are named, as I wrote, the above code looks as the column names are numbers, which won't work. Please clarify. – Doc Brown Dec 4 '19 at 20:56
  • it would be the other way around. For each product a column is created for each customer in a table. There may be 25000 customers but only 25 products. And I will make changes to the above code, my columns are p + $id not just $id – FamousAv8er Dec 4 '19 at 21:01
  • 1
    if your PHP script has the ability to alter tables, then your PHP script, after it gets exploited, will have the ability to destroy your database. – GrandmasterB Dec 5 '19 at 5:22
4

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.

| improve this answer | |
  • So it appears I still have much to learn about databases. If you have a moment I would appreciate any information you may be able to share about the first bullet in your solution. I have some instances where I have a table being altered (mostly adding and removing rows). These tables could have another user attempting to access that tables information while its being altered. Id like to find documentation or videos about how developers handle cases like this. – FamousAv8er Dec 4 '19 at 19:50
  • it looks like the correct way to do operations like this is to backup-> schedule downtime -> commit changes during downtime. If you know of anything else I may want to explore please let me know. – FamousAv8er Dec 4 '19 at 20:21
  • @FamousAv8er: concurrent access to a table which is altered is handled very differently by different database, sometimes even between different versions of the same dbms. I cannot tell you how MySql behaves. – Doc Brown Dec 4 '19 at 20:43
4

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.

| improve this answer | |
  • I have added some additional information about my use. If you could, please see if your concerns in the second part of your answer should still apply. – FamousAv8er Dec 4 '19 at 18:05
  • @FamousAv8er I've seen that. I don't think that would change my thinking about putting additional information into the DB scheme itself (be it table or column naming). – πάντα ῥεῖ Dec 4 '19 at 18:09
  • Any backup/restore process which is worth its bucks should be able to handle dynamic schema changes flawlessly, that is not a valid reason. – Doc Brown Dec 4 '19 at 21:02
  • @Doc M'key. I removed this sub. – πάντα ῥεῖ Dec 4 '19 at 22:05
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.