mysql - Let a metadata table automatically match all columnnames from original table in MariaDB

Question

In my database I have large table with a lot of columns in which each row represents a dataset. ("Table1")

I have created a second table ("Table2") which functions as a metadata-table for Table1. All the columns are identical except for an added primary key.

The main difference is that each row in this table is representing a view which has metadata for all columns of Table1

Small example:

Table1
Column1  Column2  Column3
      1     True    "Foo"
      2    False    "Bar"  
      3    False    "Baz" 

Table2
View_name  Column1  Column2  Column3
  "admin"   "show"   "show"   "show"
  "sales"   "show"   "hide"     null
"default"   "hide"   "show"   "hide"

I am now looking for a way to automatically update Table2 whenever Table1 changes. So:

• If Table1 adds a Column4, add Column4 to Table2 (values for the views can be null).
• If Table1 removes Column3, remove Column3 from Table2.
• If Table1 alters Column2's name to Col2, alter Column2s name to Col2 in Table2

And of course, while not overwriting any existing values in the Table2 for all these actions.

I am hoping to write some sort of TRIGGER function, but as an alternative I wouldn't mind running a query that does this every now and then. I just don't want to manage these changes manually.

Is there any way to do this?

Answer 1

It can't be automated using the way you've stored the data in columns.

Perhaps this is a clue that you should store the data in a different way.

Another clue is that the changes occur so frequently that you need adding and dropping of the columns to be automated!

Here's an example:

Table1 
 dataset attribute value
      1    col2    True
      1    col3    'Foo'
      2    col2    False
      2    col3    'Bar'
      3    col2    False
      3    col3    'Baz'

This is also called an Entity-Attribute-Value table. It has plenty of drawbacks too, but its advantage is that you can add or drop attributes just by inserting or deleting rows.

But you also need a table to record the canonical set of attributes that you allow.

Metadata
 attribute type    default
   col2     bool    False
   col3     string  ''

In this table too, adding a new attribute is just an insert. You can make the attribute in the first table a cascading foreign key, so if you delete a row from the Metadata table it automatically deletes matching rows that reference it.

You also need a table to annotate which attributes are to be included in each view:

Viewership
 view    attribute  show
 'admin'     col1   True
 'admin'     col2   True
 'admin'     col3   True
 'sales'     col1   True
 'sales'     col1   False
 'sales'     col1   False
'default'    col1   False
'default'    col2   True
'default'    col3   False

You are still required to manually insert rows to this table if you add a new attribute. But that's easier than adding columns.

You might be able to automate this a bit with triggers so when you insert a new row to the Metadata table, it adds a row for each view to this table. But in my experience, it's not worth the effort to use triggers. Just code it that way in your application.

(You should also assume a default that if an attribute fails to be added to the view table, it is hidden by default.)