need better relative sql query, to represent dynamic table

[jimmysk8a]

(i'm using MySQL 5.0.18)

hello,

first of all, sorry for my english, i'm not native.

an application of our company uses 3 tables (relative linked) to represent "dynamic tables". more exactly there is a table to hold TableName, one to hold attribute names, and one to hold effective data.

an example follows:

table tblname:
-------------------------
¦ IDtblname ¦ Name      ¦
¦=======================¦
¦         1 ¦ Guestbook ¦
¦-----------------------¦
¦         2 ¦ Gallery   ¦
-------------------------

table tblattr:
---------------------------------------
¦ IDtblattr ¦ IDtblname ¦ Name        ¦
¦=====================================¦
¦         1 ¦         1 ¦ Nickname    ¦
¦-------------------------------------¦
¦         2 ¦         1 ¦ E-Mail      ¦
¦-------------------------------------¦
¦         3 ¦         1 ¦ Message     ¦
¦-------------------------------------¦
¦         4 ¦         1 ¦ Timestamp   ¦
¦-------------------------------------¦
¦         5 ¦         2 ¦ ImgSrcSmall ¦
¦-------------------------------------¦
¦         6 ¦         2 ¦ ImgSrcBig   ¦
¦-------------------------------------¦
¦         7 ¦         2 ¦ Comment     ¦
---------------------------------------

table tbldata:
----------------------------------------------------------------
¦ IDtbldata ¦ IDtblattr ¦ rowid ¦ data                         ¦
¦==============================================================¦
¦         1 ¦         1 ¦     1 ¦ Peter                        ¦
¦--------------------------------------------------------------¦
¦         2 ¦         2 ¦     1 ¦ peter@lala.net               ¦
¦--------------------------------------------------------------¦
¦         3 ¦         3 ¦     1 ¦ Hallo, meine Nachricht ...   ¦
¦--------------------------------------------------------------¦
¦         4 ¦         4 ¦     1 ¦ 1125518784                   ¦
¦--------------------------------------------------------------¦
¦         5 ¦         1 ¦     2 ¦ Jimmy                        ¦
¦--------------------------------------------------------------¦
¦         6 ¦         2 ¦     2 ¦ jimmy@your.com               ¦
¦--------------------------------------------------------------¦
¦         7 ¦         3 ¦     2 ¦ Testnachricht, blabla ...    ¦
¦--------------------------------------------------------------¦
¦         8 ¦         4 ¦     2 ¦ 1125910044                   ¦
----------------------------------------------------------------

the resultant table from tblName = "Guestbook" could than be:

-----------------------------------------------------------------------------------
¦ rowid ¦ Nickname ¦ E-Mail         ¦ Message                     ¦ Timestamp     ¦
¦=================================================================================¦
¦     1 ¦ Peter    ¦ peter@lala.net ¦ Hallo, meine Nachricht ...  ¦ 1125518784    ¦
¦-----------------------------------------------------------------¦---------------¦
¦     2 ¦ Jimmy    ¦ jimmy@your.com ¦ Testnachricht, blabla ...   ¦ 1125910044    ¦
-----------------------------------------------------------------------------------

now, the user should have the possibility to set up queries to the resultant table, as if it would be real. he doesn't know about a relative representation of his table in the real db.

a function in the application interprets the WHERE clause of the User Query, and generates a correct query fitting the relative representation of the table.

up to now, this works as long as there are only ORs in the WHERE clause, eg.:

... WHERE (Name = 'Jimmy') OR (Message LIKE '%Hallo%')

will get..

...  WHERE ((IDtblattr = 1) AND (data = 'Jimmy')) OR ((IDtblattr = 3) AND (data LIKE '%Hallo%'))

but as soon as there's an AND in the WHERE Statement, i can't make a selection, because there are more than one resulting row affected.
eg:

... WHERE (Name = 'Peter') AND (Message LIKE '%Hallo%')

i've found a very ugly, stupid and low solution, takes up 12 seconds to resolve:

SELECT
  DISTINCT tbldata.rowid AS IDrow,
  (SELECT data FROM tbldata WHERE (IDtblattr = 1) AND (rowid = IDrow) LIMIT 1) AS `Name`,
  (SELECT data FROM tbldata WHERE (IDtblattr = 2) AND (rowid = IDrow) LIMIT 1) AS `E-Mail`,
  (SELECT data FROM tbldata WHERE (IDtblattr = 3) AND (rowid = IDrow) LIMIT 1) AS `Message`,
  (SELECT data FROM tbldata WHERE (IDtblattr = 4) AND (rowid = IDrow) LIMIT 1) AS `Timestamp`
FROM
  `tbldata`
WHERE
  rowid IN
           (
             SELECT rowid FROM tbldata WHERE (IDtblattr=1) AND
                                             (data = 'Peter') AND
                                             (rowid IN
                                                       (
                                                         SELECT rowid FROM tbldata WHERE (IDtblattr = 3) AND
                                                                                         (data LIKE '%Hallo%')
                                                       )
                                             )
           )

as you certainly know, the WHERE clause must be like that, because i can't access the subselected attributes.

i know, this is very ugly, and i'm searching for a better solution. please help me to find one! perhaps is there a solution with GROUP BY, or JOIN? i'm not very familiar with this kind of statements.


many many thanks in advance
Jimmy

 

[r937]

searching for a better solution? redesign your tables

see OTLT and EAV: the two big design mistakes all beginners make

r937.com | rudy.ca

 

[jimmysk8a]

thx i know the problem :-s

how would you represent a dynamic table structure instead of this one? i don't want to use temporary tables.

any suggestions are very appreciated

 

[r937]

how would i represent dynamic tables? i wouldn't

how could it be done, if i weren't doing it? probably using your design

the queries are tough, though, aren't they ;-)

r937.com | rudy.ca

 

[jimmysk8a]

this doesn't brings me further

the application needs this feature for a "user editable data store", this is a requirement of our software architects, i'm only an operator :-s

so next approach i think is to make a view to select, and stored procedures to insert and update, what do you think about that?

or any other suggestions?

 

[r937]

i would ask the software architects (a) exactly what they mean, and (b) have they ever done this before

r937.com | rudy.ca

 

[jimmysk8a]

(a) i know exactly what they mean.

here the requirements:
- an Online CMS has integrated a kind of Form Processor and Form Logic, which can be set up and controlled by a CMS Admin.
- the CMS Admin is able to create a Webform which stores the filled out data from website viewers in a "dynamic table"
- the CMS Admin has now the possibility to use this different defined stores to show it on other sites in HTML Tables, sort them, select special parts of it, and so on.

(b) yes, they have, but with "CREATE TABLE"

i'm searching for another solution, or is creating temporary tables the good one?

(i like the easy way of adding a new attribute to a already created table, and this is very easy in a relative representation)

one other way is to fully program a datastore in the code, with no use of db....