How can I create a database where the user can define the fields and types of the detail table in the MD relationship?

Question

In my application, there is one table called “events”, and each event has about 30 standard fields, as well as user-defined fields, which can be any name or type in the “eventdata” table. Users can define these event data tables by specifying x the number of fields (either text / double / datetime / boolean) and the names of these fields. This "eventdata" (table) may be different for each "event".

My current approach is to create a lookup table for definitions. Therefore, if I need to query all the “events” and “events” for each record, I do this in the MD relational space using two queries (i.e., select * from the events, then for each record in the “events” select * from "some table",).

Is there a better way to do this? I have implemented this so far, but most of my queries require two different calls to the database: I can’t just join my "events" table with different "eventdata" tables for each record in "events".

I think my main question is: can I join my main table with different detail tables for each record?

eg.

SELECT E.*, E.Tablename 
FROM events E 
LEFT JOIN 'E.tablename' T ON E._ID = T.ID

, , , , .

Answer 1

.

• "Custom1", "Custom2", "Custom3" .. varchar (?) similiar

• (, XML).

• /, - . . , .

• , MongoDB NoSql, .

: , , . . SalesForce CRM .

, . . , .

, , , . SalesForce ; , . , Microsoft, , , . , , bullcrap; . , .

. , , . .

Answer 2

, eventdata, . .

MySQL: - MySQL

Answer 3

, , .

- :

Event (EventId, ..., ...)
EventColumnType (EventColumnTypeId, EventTypeId, ColumnName)
EventColumnData (EventColumnTypeId, Data)

, (, ), .

Answer 4

, , EventData .

, EventCustomFields, , ( + PK) .

, . . : , (yuck x10)

, (, ) , , . ,

Select E.*, C.*
From events E, customFields C
Where E.ID = C.ID

Answer 5

? , Sharepoint 2007 , CustomString1, CustomDate2 .. , , ( , .

Answer 6

: no. . , . , , , .

, () . . , "foo" "", string0 , "", string1, "reportDate", date0. , . , , "" . , , , .

If you are really hardcore, you can write a database procedure to query the table structures and serialize all the key / type / value roots in Lilst and return them to one long row as the last column, but this is probably not much more than that. What are you doing now.