Table with an unknown number of columns

Question

We are developing a tiny database that will contain data on the use of our software. Therefore, in our programs / websites we will call a small service that registers some data about the session and the completed action. Thus, we can see which parts of our programs are heavily used, which are the most common use cases, etc.

The part I'm struggling with is how we persist in different actions. Since we do not know what exact actions and parameters will be required by all applications and future applications, it is difficult to determine the data structure.

Currently, it looks something like this:

   Actions
--------------
+ Id
+ ActionTypeId
+ SessionId
+ TimeStamp
+ Data01
+ Data02
+ Data03
...
+ Data10
+ DataBlob

I especially doubt all data fields. In practice, this will either be a path to many columns, or too few. All concatenating them in one field will be hell for a request.

Are there any suggestions?

Answer 1

Use another table,

Data
---------
+ Value
+ ActionId

and then combine both tables, as in

select Value from Data, Action where Data.ActionId = Action.Id and ...

Answer 2

One approach is to store the flexible part of the data schema in an XML field - in SQL 2005 there is an XML data type that can be indexed and queried without the pain you used for pre-SQL 2005.

, , , .

Answer 3

ActionsData, , .

ActionID
Property
Value

Answer 4

 Actions
 --------------
 + ActionID
 + ActionTypeId

 Actions-Log
 --------------
 + ActionID
 + LogID

 Log
 --------------
 + LogID
 + SessionId
 + TimeStamp
 + Data

, , , XML .

Answer 5

, CouchDb? , , / , .

, :

• Id
• ActionTypeId
• SessionId
• TimeStamp
• DataObject

Answer 6

The standard answer would be to put the data values ​​in a separate table with the identifier from the action table as a foreign key in the data table. That is, the action will look like this:

Id
ActionTypeId
SessionId
TimeStamp

Then you will have a data table that looks something like this:

ActionId
DataType
DataValue

Answer 7

You can also look at the observation pattern, as in this question / answer . I am your example . Subject = Action