1

I'm working on a project in which user can create their own web services from UI by simply selecting number of fields (Max 200) and defining datatype for each field. I'm considering following table structure for the same :

ServiceMaster
+-----------+-------------+-------------+
| ServiceId | ServiceName | FieldsCount |
+-----------+-------------+-------------+
| 1         | Service1    | 2           |
+-----------+-------------+-------------+
| 2         | Service3    | 3           |
+-----------+-------------+-------------+

ServiceDetails
+------------------+-----------+--------+---------+-------+-------------+
| ServiceDetailsId | ServiceId | Field1 | Field2  | Field3| Field...200 |
+------------------+-----------+--------+---------+-------+-------------+
| 1                | 1         | 5      | Active  | NULL  | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+
| 2                | 2         | High   | 9.0     | 7     | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+
| 3                | 1         | 2      | Running | 7     | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+

I'm not adding DataTypeDetails table here for clean and simple code, but yes I have one table to hold datatype for fields. If you see above table structure, I can define Varchar or any string datatype for all Fields, but if I do that then while applying filters on table I have to CAST fields data with it's original datatype. Like, 9.0 (Float) or 7 (Int) etc. I think this will slowdown query if I will use TYPECASTING for all columns to apply filters. This able may have Billions of records or more.

So is there any alternate way to achieve the same with better performance.

Improve this question
1
  • EAV models are problematical; you have made such worse by having multiple datatypes. Use JSON as the least of the evils. – Rick James Apr 30 '17 at 14:55
1

This is basically a question about a multitenant application; and cal also be thought of as a particular application of an Entity Attribute Value scenario.

You can have different approaches, on top of your current one:

  1. Have one ServiceDetails table per user/service (per tenant), with fields defined according to his/her/its needs. Your query will use ServiceDetails1 ... ServiceDetailsN tables, instead of just 1. Instead of having your table DataTypeDetails, you can use the database metadata (information_schema) if needed. This is the most query friendly way of doing things.

  2. Instead of having field1, field2, ..., field200, have a much larger collection of fields: field1_text, field1_integer, field1_float, field1_timestamp, field2_... and use the version of your column that has the proper type for your specific case. You will have an enormous amount of NULLs, and probably a lot of indexes. This uses only (a very large and sparse) table. It's not normally the best approach, but it is a possibility.

  3. Use a database that allows for more flexible structures (such as JSON) for variable-schema. (PostgreSQL JSONB data-type, which can be indexed, would be a good fit for this scenario; even if you will still need to cast types).

References:

Improve this answer
6
  • Thanks @joanolo I think 1st approach is not good in my scenario, because services can be in millions, so having different table for each service is not good. About 3rd, database is already decided and it will be MySql only. Now I can think about 2nd approach, but same time I want to know about performance issue, columns will be 200*(NumberOfDataTypes), even if I choose 5 datatypes then 200*5=1000 columns will be created and most of the services using only 5-20 columns only (only few services use 20>=200). So how much it will impact on performance as around 980-995 columns will have NULL values – Shri Apr 29 '17 at 11:14
  • 1
    If services can be in milions... make sure your decision about database scales well enough. Test first with a mockup. – joanolo Apr 29 '17 at 12:40
  • 1
    ok @joanolo, We chosen MySQL to work with PHP, even though if this issue will not resolve in MySQL then I may think about other database. So what is your database suggestion if I have millions of services. I just want open source database like MySQL and it should support well in PHP. – Shri Apr 29 '17 at 13:56
  • 1
    If you're talking milions, test carefully your solution with a setup that simulates thos millions well enough, no matter what choice of database you make. MySQL is one solution that handles websites with millions of things. I'd probably check as well, in a realistic scenario, most probably PostgreSQL, and maybe some non-relational approaches (MongoDB and Neo4J come to mind as first-to-check). See what fits better your purpose. They can easily be interfaced from PHP, Python, Node.JS ... – joanolo Apr 29 '17 at 14:47
  • 1
    Finally I decided to move to PostgreSQL, thanks @joanolo. – Shri May 2 '17 at 18:34
1

In addition to the models presented you can also add separate tables for each datatype and link them to the services. This way you have only data for the fields you need and will not waste space on unused columns.

This may, however, require lots of joins and they have to be dynamic, but they can also use the datatypes directly. May be slow, but that can only be determined by testing.

I would also care more about validity than performance with the original way since converting everything to string and back may not be reasonable.

Improve this answer
6
  • Thanks @Sami it looks similar to 'joanalo's' 2nd solution, only difference will be, instead of adding all columns in same table we will create different tables for each datatype. Something like TableInt with 200 columns, TableVarChar with 200 columns ............so on. As per my fields dataytpe I have to distribute my data into different tables. But EOD result will be same, same number of columns but distributed in tables, same Number of NULL fields but in different tables instead of in Single table. Please correct me if I'm wrong here. – Shri Apr 29 '17 at 16:55
  • @Shri No, no 200 columns. Each field has their own row in the table. So if it has five fields you have five rows in n tables. The table will have columns for link to the service, the field id and the value. – Sami Kuhmonen Apr 29 '17 at 16:57
  • Ok .. So it's something like @Barmar's suggestion if I'm not wrong. – Shri Apr 29 '17 at 17:02
  • It looks good @Sami, But as you already mentioned it will be slow because, 1] We are storing data in multiple rows for single entry. 2] Different tables for each datatype (JOINS are required). I think it will make queries more complex and time consuming. I agree with you that this can be a good solution if we don't have any other option. – Shri Apr 29 '17 at 17:16
  • Number of joins in this solution does not seem excessive to me. Pretty commonplace for mysql (and other RDBMSs too). – Lauri Laanti Apr 29 '17 at 18:41
0

I suggest a table like this:

ServiceDetails
+------------------+-----------+----------+--------+---------+---------+
| ServiceDetailsId | ServiceId | FieldNum | ValInt | ValFloat| ValChar |...
+------------------+-----------+----------+--------+---------+---------+
  1                  1           1          5        NULL      NULL
  1                  1           2          NULL     NULL      Active
  2                  2           1          NULL     NULL      High
  2                  2           2          NULL     9.0       NULL
  2                  2           3          7        NULL      NULL
  3                  1           1          2        NULL      NULL
  3                  1           2          NULL     NULL      Running
  3                  1           3          7        NULL      NULL

The ValXXX columns should be nullable, and only the data type that's used for that column should be filled in. This makes the storage for the unused fields reasonably efficient.

This is similar to @joanolo's second recommendation, but avoids running into limits on the size of table rows from having multiple fields for each field number.

Improve this answer
1
  • Thanks @Barmar I need to check how much this solution is good fit for complex queries. I have to think about your and 'Sami's' solution, as your solution is good if I want to avoid extra JOIN and 'Sami's' solution is good if I want to avoid NULL values (Waste of sapce). – Shri Apr 29 '17 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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