Dynamic database

[poweraktar]

Hi guys,
I am creating a database within a database so that I can allow the user to create their own tables etc and retrieve the data they input.
I am aware this is very inefficient however, my client requires it and I need to crete it :)

I've got past the bit where they create the database and input the data
but retrieving the data is not so easy.. which is why I'm here

The database structure looks like this:

dataset
DatasetID(PK AI), DB_name, DB_description etc..

This table holds basic details about the table. The only significant field is the DatasetID as it will be used to relate fields to it.

Fields
FieldID (PK AI), DatasetID, Field_name, Data_type

This table is used to keep a list of the fields associated with the dataset.
A scritp will look through this table and dynamically create a form based on the information held in this table

records table
DatasetID, UserID, RecordID (PK AI)

Everytime a user submits the form, a new entry will be created. The RecordID will be carried in this form and passed to the value tables


To hold the data, I have created a few value tables, all of which have the same fields. I've done this so at least I dont have to use varchars for every single entry, which would make this venture more inefficient

So the structure of these tables are like this:

data_char_{max value length} eg data_char_10

ValueID(PK AI), RecordID, FieldID, Value

Now when the user inputs the data, the value of each field is inserted into one of the above value tables, depending on the length of the value.

As you can see, my eggs are is so many different baskets.

What I need to do is concat all the values of all the value tables for each of the field and mask it as the original field name.

Heres a sample of what I've done so far, but I need to be able to attach a condition to the concat to get accurate results:




SELECT data_records.RecordID,

CONCAT_WS(' ',data_float.Value, data_char_10.Value, data_char_25.Value, data_char_75.Value, data_char_125.Value, data_char_175.Value, data_char_225.Value, data_char_255.Value, data_blob.Value, data_date.Value, data_enum.Value, data_set.Value) phone_number


FROM data_records LEFT JOIN data_float USING(RecordID)

LEFT JOIN data_char_10 USING(FieldID)
LEFT JOIN data_char_25 USING(FieldID)
LEFT JOIN data_char_75 USING(FieldID)
LEFT JOIN data_char_125 USING(FieldID)
LEFT JOIN data_char_175 USING(FieldID)
LEFT JOIN data_char_225 USING(FieldID)
LEFT JOIN data_char_255 USING(FieldID)
LEFT JOIN data_blob USING(FieldID)
LEFT JOIN data_date USING(FieldID)
LEFT JOIN data_enum USING(FieldID)
LEFT JOIN data_set USING(FieldID)

WHERE DatasetID = 16




Any help or suggestion of alternative approach to this problem would be appreciated

I've attached the entire db structure with this post