This question is about refactoring existing database design.My data flow isUser generates some data for product lines A, B, CData is saved into the database onceData is later retrieved multiple timesCurrent design has 3 tables:  data_a, data_b, data_c, where each table shares some columns that are identical (in name) and some that are unique to that product line.For example, same-name columns in each table are weight, unit_system and a few others.  The differently-named columns have values that represent physical quantities of the particular product line.  Those are named using various alphanumeric identifiers, like a, b5, e2, and there is a different set of them for different product line.  Those sets can share elements, i.e. b5 can be in more than one table, but then something like t1 can be in one table but not the others.ProblemCurrently when there is a need to add some value say x9 to product line a, I would update the database schema for data_a to have column x9.  I make the values of x9 as 0 for existing column rows, and new records will begin to populate with the actual x9 values.  Then I update the code in relevant places to insert x9 into the table or retrieve it from the table.Existing designdata_a(id, item_id, shared, different_a)data_b(id, item_id, shared, different_b)data_c(id, item_id, shared, different_c)where shared columns is a group of columns that is identical in each table, while different are columns that are disjointed in theory, as they represent 3 different product lines, but actually may share some similarly-named elements, as some variable names are the same for different product lines.  Proposed designThis is where I'm struggling.  Because I don't see a good clean design that is also efficient.  I wanted to get rid of the need to alter database schema every time there is a new variable added to a product line.  And I believe I can do that, but I also want to make an efficient design, and I don't see one.But this is my try:Keep primary key, foreign key and shared column names in a single table:data(id, item_id, shared)Create a single table for variables only (variables are ones found in different sets):data_variables(id, item_id, data_id, variable, value)I am not sure if this design will be worth the trouble, because ... I will actually be storing more data - all the extra data_id or all the extra item_id values for each variable name.  There are 15 to 30 variable names for each product line.  I will be storing 15 to 30 item_id (or data_id) fields in the new design data_variables table, where in the old design there was only one item_id value per table row.Question:Is there a more efficient design that also does not require changes in schema design for every addition/deletion/modification of variable name in a product line?  Might it be best to stick with existing design despite the trouble of altering schema when needing to add new variables?Using JSON for variable different fields one_data_table(id, item_id, product_line, shared, json_encoded_value_pairs);Decision to not use EAV (Entityattributevalue) ModelIn my case Entities change very rarely if at all (on the order of years), and attributes change rarely as well, on the order of months or more.  As such, reworking the database design to use EAV is probably not a good fit for my case.That aside, I am still debating on my JSON Design.
What is an efficient design to store variables for different product lines in ER database?
database design;mysql