0

I am not sure if SO is a good place since I am looking for recommendation and guidance on a resource to learn about a use case at work.
I am looking to get some recommendation on the database design approach for a following use case.

Let's say that we have a user interface where a user can come and define certain dimensions along with their type

age, Integer
name, String
credit score, Integer

And then, the user imports a CSV, that contains the headers which are nothing but dimensions that he has defined above.

age, name, timestamp 32, Ari, 560 44, Ben, 750 29, Ada, 700

How can define this relationship? A table containing the dimensions with type and another one which contains the data where columns match the dimensions?

Dimensions to Data

Additionally, the user can later change dimensions (add or delete) and pass the corresponding file with those headers. Now as I see, the data is out of shape with the previous import. How can we achieve this using a relational DB? Can we minimize the Alter Statements or create dynamic tables for everytime the dimensions change?

Dimensions 

age, Integer
name, String
credit score, Integer
ltv, Double

Data
age, name, timestamp, ltv 32, Ari, 560, 0.34 44, Ben, 750, 0.76 29, Ada, 700, 0.88

Any guidance on resource to learn from are very much appreciated.

daydreamer
  • 87,243
  • 191
  • 450
  • 722
  • Is this mysql or posgresql the system tables which list the table/column and datatypes is what you need and these vary by system. It seems like you just want to ensure that the table created has the same structure as the dimensions created. How will you make this check? how is the data being loaded? The issue at hand is you have DDL info in your dimenions. DDL (data definition language) Defines the structure of an object; relationships are maintained between the data in a different structures, not the data and structure. So there is no relationship to define here.... – xQbert Dec 07 '18 at 21:19
  • Now if you simply want a cross check to ensure that the DDL define matches the data structure created; you could compare the appropriate system table to the dimensions entered. This could be done by compairing the Dimenion metadata entered to the system records created for the columns in the table. `from information_schema.columns` is what the system object is I believe. – xQbert Dec 07 '18 at 21:23
  • If you structure the tables more or less like **user** id, name, **csv** id, user_id, name, **dimension_type** id, csv_id, name, type (best is the use native MySQL datatypes as string like `VARCHAR(255)` or `DOUBLE(2, 2)` so you can CAST the data later in a VIEW) ,**dimension** id, dimension_type_id, name , **data** id, csv_id, dimension_id, value (this is where you can use the CAST from VARCHAR(255) into a native MySQL datatype), column_position.. Then you don't need to use ALTER statements offcource you are going to need application code to convert the CVS into this structure – Raymond Nijland Dec 07 '18 at 21:30
  • Sounds like you are asking for `LOAD DATA INFILE ...`?? – Rick James Dec 07 '18 at 22:20
  • Above and beyond the duplicate links, see the following topic that lists the available options: https://dba.stackexchange.com/questions/58036/how-to-handle-table-design-with-variable-columns – Shadow Dec 08 '18 at 00:26

0 Answers0