1

I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data.

The task I am currently struggling is: user needs to enter data column by column, which would consist of a number n of ID's , a number m of attributes and a number k of classes, with the conditions that n, m > 0 and k >= 0. Data is heterogeneous, so indexes can be both numeric or text, and the same goes for attributes and classes. I'm supposing there will be no null in the data for simplicity.

I'm currently thinking on:

  1. Creating a table with more than enough columns (all with null values), so that I can work using only the non-null columns (which will be got from user input). However this would limit the size of the datasets people could input.

  2. Resorting to create an specialized data structure on a programming language, do all the work there and finally, create a table dinamically to store the result data there.

  3. Using a database specialized for this kind of data (maybe a document-based DB).

  4. Create a data structure on the RDBMS itself (I'm using PostgreSQL), let's say a variable size array, so that I can create the table directly from the user input, using only 3 variable arrays (one for indexes, one for attributes and one for classes). However, I keep in mind that attributes and indexes could be of different types, so the array would have to support heterogeneous data type and I don't know if this is possible on a RDBMS or SQL.

I've been looking for information on information but got no result until now. Any guidances to a package, language library, extension or paper, thesis, technical report with relevant information would be appreciated. Also, personal experiences with doing something similar could be useful.


Edit:

Some example records:

ID NAME   CODE  HEIGHT WEIGHT       CLASS1     CLASS2
1  HARRY  100    170    70         SURVIVOR    VICTIM 
2  ALBUS  101    185    82      
4  REMUS  103    177    60

This is an example dataset where the intention is predicting if a person will survive or die based on its height and weight. The output should be lines consisting of SURVIVOR or VICTIM lines.
The ID's would be ID, NAME and CODE columns; HEIGHT and WEIGHT are the attributes and CLASS1 and CLASS2 are the classes.

The same structure should support something like this:

ID   CODE  INCOME  AGE  NUMBER_OF_HOUSES  CHILDREN   C1    C2   C3
1    101   9000    21       2                NO      HIGH  LOW  MED
2    110   5700    30       1                YES                
7    111   4000    37       0                TES

In this example, the idea is to preddict the future level of income of a person, based on it's actual age, income, number of houses and if they have children currently. The result is either HIGH, MED or LOW.

The data in this case would be: ID and CODE represent the ID's, AGE, NUMBER_OF_HOUSES, INCOME and CHILDREN represent the attributes, and C1, C2, C3 represent the classes.

The data structure should support both inputs, even though they have different number of columns for each category. And also, each one of the columns have different data type. The number of columns per category shoudn't be fixed.

closed as too broad by Tom V, Vérace, Philᵀᴹ, RolandoMySQLDBA, Daniel Vérité Feb 3 '16 at 10:11

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • It doesn't sound like a task best suited to a RDBMS given the information provided. Could you give an example "use case" of how someone would use this system?....for example - what is the "domain" for the data they are entering and is there particular business logic. – seventyeightist Feb 1 '16 at 19:26
  • 1
    @seventyeightist Yes. Suppose you want to apply regression techniques on a data set. The user inputs the ID's, the attributes and the classes. Then, the data is processed using several regression techniques, using a set of parameters. The results are shown to the user, specifying first the model, second the parameters, and third the data. After that, he can decide which model provided a better result, so that he choose that one and can re-run the model again by deciding the modeling engine to try another (bigger) sets of parameters of by inputing his own set of parameters. And so on. – addictedtohaskell Feb 1 '16 at 19:35
  • 1
    @Vérace I have already appended some examples. Let me know if they help to understand. – addictedtohaskell Feb 1 '16 at 20:13
  • 1
    You should look at Postgres's HSTORE extension?.. (Or possibly JSON columns) – Joishi Bodio Feb 1 '16 at 22:22
  • 1
    Apart from Postgres' "NoSQL" features like hstore and json you could read up on an anti-pattern named "entity-attribute-value" (EAV). Be sure to understand the problems that model gives you before you implement it (I would always prefer a hstore over that) – a_horse_with_no_name Feb 1 '16 at 22:53
1

Given your "irregular" data, you might want to look at Magneto (see here and here). Originally designed for the clothing industry which can have many different products with many different attributes. It might just be a fit for your needs. There is a community edition so you can look at the code and (esp.) the database table structures and adapt to your own environment.

Be warned though! It is not a panacea - there are problems that come with the added flexibility. See my answer here which explains some of the pitfalls (be sure to follow the links) of this approach (known as EAV).

  • That's a complete shop solution written in PHP - it's not a DBMS (plus it doesn't support Postgres which addictedtohaskell is using). It most likely uses an anti-pattern known as "entity-attribute-value" to store those attributes in the database. – a_horse_with_no_name Feb 1 '16 at 22:52
  • I didn't say implement Magneto - I just said look at it, it might give some ideas. Secondly, my answer (linked) gives plenty of warning about the potential pitfalls of EAV. Finally, it may be an antipattern, but it appears to suit some needs. Respectfully submitted. – Vérace Feb 1 '16 at 23:00
  • Does EAV anti-pattern allow to create a generic table in advance and then fill it with the user provided input or is it necessary to create it dynamically? – addictedtohaskell Feb 1 '16 at 23:38
  • It's one way of doing it. Take your two sample entities above. Entity1 would have attributes Id, name.... class1, class2 (7 in total) and entity2 would have attributes I'd, code,,,,c2, c3 (9). I would urge you to also investigate the hstore and json options proposed elsewhere in this thread. – Vérace Feb 2 '16 at 0:47

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