CODEWITHSUNDEEP
phpmysqldatabasedata-structuresrelational-database
HNA
HNA

Reputation: 307

Storing Dynamic Data & Data Types in MySQL

I am building a data storage and visualization system using the traditional LAMP stack.

The users need to be able to define the data types(string, int, decimal, boolean) and restrictions (length, decimal places, auto increment, etc) for the data rows they will be submitting. The only two constants are the auto incremented ID in the first column and the Current_Timestamp in the second.

Examples of user data:

23, 2017-04-19 12:00:00, 239, 1044.72, 0, 0, 1, 0, 1

-

931, 2017-04-19 12:00:00, 200, 12

Currently, these are the solutions I thought of:

Option 1

Allow the user to modify the table structure by converting their selections to the SQL alter table equivalent. To keep it simple, let's assume each user gets a single table and that once it's setup it never needs to be modified.

UserData

ID, TS, UserValue, UserValue2, UserValue3....

Option 2

Create String, Decimal, Boolean, etc. tables that hold data for all users with a Foreign Key to the user and another Foreign Key to a table holding additional options for the variable type (the options for the column holding the data would be configured to hold the largest possible values that could be entered by any user).

Strings

ID, TS, Value, User(FK), VariableType(FK)

Decimals

ID, TS, Value, User(FK), VariableType(FK)

etc.....

The data needs to be searchable so solutions using strings for all data won't work.

Do either of my solutions seem appropriate or am I missing something here?

Upvotes: 3

Views: 2014

Answers (1)

Walker Boh
Walker Boh

Reputation: 770

If you see no issues allowing users to alter the table to suit their needs, then it's a viable option. Otherwise, if you want a standardized database structure everywhere, consider adding a column that uses the JSON Data type. This data type will allow your users to store any data they want as a JSON object in this column. Then your users can query for specific values from the JSON column and then handle the data as a regular JSON object in their code. JSON object keys can also be indexed for faster querying if need be.

Upvotes: 4

Related Questions