Working with disassembled records in a poorly designed database
Overview
I inherited a website that allows users to order customized products. The configurations have been saved in such a way as to separate them from writing. I would like to modify the db so that these entries can be linked.
Example
Users can get item # 1 "stock" or customize it by modifying up to 10 different properties. Let them say color, fabric, width, height, etc.
Orders can and regularly fulfill several products, each of which can be customized.
In addition, users can save their orders so they can reorder later.
When the database was created, the order details were neatly organized into separate columns. Customer name, address, type of payment, etc. But the product list and more specifically their settings were stored as a JSON string in one column. For convenience, call this column "cart".
Basically, the table order
has a column cart
, and the column cart
contains a list of products and settings, formatted in JSON format.
Unfortunately, the JSON object has referenced ids in the table product
, but no references to the table customisation
. Instead, it uses a bunch of human readable strings. Fortunately, these lines exist in the table customisation
, but they were written as the cart was created.
The problem we are facing is that the list of settings can be changed by the CMS. Until now, they have not been changed. 🙏 But they will need it soon and this will cause problems:
Problems
-
If a personalization parameter is removed (for example, a fabric parameter) and a customer reorders from an old saved order, we need to be able to parse the cart, detect it, and alert about changes.
-
Moods are currently immutable. Once a product is added to the cart, it cannot be changed. Users need to be removed and re-added to make one change. Bad UX.
-
If someone changes the human readable text to
customisation
, we are dead. ☠️
Questions
-
How would you design this if you looked from scratch?
-
How can we convert the current version and legacy data to this new schema?
I don't know if the stack is conspicuous, but we're on Postgres and Django-Python.
source to share
I would accomplish this with the following tables:
Products {
productId // primary key
name
price
}
Customization_Types {
customizationTypeId // primary key
name // e.g. COLOR, FABRIC, LENGTH
}
Customizations {
customizationId // primary key
customizationTypeId // foreign key
value // e.g. BEIGE, VELVET, 8
}
Product_Customizations {
productCustomizationId // primary key
productId // foreign key
customizationId // foreign key
priceModifier // price markup for applying the customization
isValid // false if this record is invalid/obsolete
}
Orders {
orderId // primary key
customerId // foreign key
}
Product_Orders {
productOrderId // primary key
orderId // foreign key
productId // foreign key
quantity
}
Customization_Orders {
customizationOrderId // primary key
productOrderId // foreign key
productCustomizationId // foreign key
}
The product table contains data for your base products - name, price, etc.
The Customization_Types table contains type names for different settings - COLOR, FABRIC, LENGTH, etc.
The Settings table contains a link to the customizationTypeId as well as the legal value. I assume that users cannot enter arbitrary numeric values (like LENGTH or WIDTH), i.e. instead, they are given a dropdown of a textbox, however if they can enter arbitrary numeric data, then you need MIN / MAX fields that are null for named constraints (for example, you could be of type: COLOR / Value: BEIGE / Min: NULL / Max: NULL or Type: Length / Value: NULL / Min: 4 / Max: 8)
The Product_Customizations table binds the customization to the product, for example, if ProductX can be in BEIGE, then you would create a Product_Customization record that links ProductX to BEIGE.
The Orders table simply contains the orderId and everything else related to the order (for example, a reference to the customerId and shippingAddressId).
Product_Orders associates a product with an order
Customization_Orders links Product_Customization to Product_Order
Let's say a customer orders ProductX in BEIGE and LENGTH = 8, then you should create an Order record, a Product_Order record with a link to ProductX, and two Customization_Order records - one associated with COLOR = BEIGE and one associated with LENGTH = 8.
This should make it easier to change product settings without having to reboot the entire product - the user can change the color to COLOR = RED without touching the length setting (delete the old Customization_Order: COLOR = BEIGE entry and create a new COLOR = RED), or the user can remove the length setting without touching the color setting (delete the old Customization_Order: LENGTH = 8).
When re-uploading an old order / product, you can quickly verify that the same productCustomizationInd conditions, otherwise a custom flag, still apply to the product in question. In addition, you can mark the user if the customization still applies, but the customization price modifier has changed.
In terms of converting legacy data, I'm not familiar with Python, but I have experience reading JSON through Java, and I assume that Python offers similar, if not better, libraries for this. The trick will match the existing data with the preloaded Product_Customization data - if the data does not match, then create a new Product_Customization line matching it with isValid = FALSE (assuming this setting is no longer offered) and when you get the option to manually iterate over invalid Product_Customization lines. to ensure that these are actually unmatched settings and not just parsing errors.
source to share
Slight improvement on Zim-Zam's answer.
An even better approach is to store not simple values (BEIGE, VELVET, 8) as settings, but as a schema from which the code can create the correct kind of setting.
It can only be JSON / XML text. And the organization that is responsible for building the view and applying the logic needs to be able to work with different versions of JSON data.
For example, if the customization properties are changed and something new is added, in this case you only need to change the code and adjust the JSON will be saved. There is no need to modify existing data. It should also be possible to read old versions of JSON with old properties and work with it.
Two possible ways to do this if you read an old object from the database:
- View builder ignores all old customization properties, adds new properties and sets their default values. I would go with this personally.
- The old view is presented to the user, but when the user clicks, for example, the OK or Finish button, additional logic checks for the old properties and notifies the user to manually remove them or simply remove them automatically.
A more flexible approach, requiring only code changes, not touching the db, and allowing old custom settings to be shown if needed.
Update: There can be two types of properties in settings: one that the administrator can define, such as the name or price, which do not change often and are common to all settings, and the other, such as size and color, which can be changed often, can be user-defined values and are not common to all settings.
The first view should be stored in the customization table as separate columns. This will allow you to change such properties in the administrative panel and reconcile all previously saved data.
The second type of properties can be 1) changed frequently 2) not all types of settings can have such properties. It is a bad idea to keep them as separate columns, because if there is a huge amount of data, changing the column type or adding a new column can lead to performance degradation, and sometimes not possible due to incompatible property types. In fact, if they are stored as separate columns, you may have to change your code to support the new properties.
My idea is that you still allow the administrator to change the type of such properties and add new ones or remove old ones through some interface. The main point here is that you are storing JSON data like
{
"properties": {
{
"propertyName": "height",
"propertyType": "int",
"min" : 10,
"max" : 25,
},
{
"propertyName": "color",
"propertyType": "color",
},
{
"propertyName": "anotherCustomField",
"propertyType": "text",
},
}
}
It remains to be done to implement view builders or renderers for all property types. And add a separate table with only values. You took the customization record from db, you found out what customization properties are, checked which one is still valid and only displayed in valid ones. If the admin changed the type of the customization property or just deleted it, you noted that the customization property is invalid in the db and that all work. No code changes, no database schema changes.
source to share