Tutorial :Table with unknown number of columns


We're in the process of designing a tiny database that will contain data regarding our software's usage. So in our programs/websites we will call a little service that logs some data regarding the session and the action performed. This way we can see what parts of our programs are heavily used, what the most common usage scenarios are etc.

The part I'm struggling with is how we are going to persist all the different kinds of actions. Because we don't know what exact actions and parameters all applications and future applications will be needing it is hard to decide on a data structure.

Currently it looks something like this:

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

I'm particularly doubtfull about all the datafields. In practice it will be either way to many columns or way too few. All concatenating them in one field will be hell to query on.

Have any suggestions?


Use another table, with

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

and then combine both tables, as in

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


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

Alternatively, have a main table with the common data, that then links to separate tables containing action type specific data.


How about an ActionsData table, with a row for every piece of data and foreign key pointing to the appropriate action.

ActionID  Property  Value  


Add more tables and set up relations

 Actions   --------------   + ActionID   + ActionTypeId     Actions-Log   --------------   + ActionID   + LogID     Log   --------------   + LogID   + SessionId   + TimeStamp   + Data  


Since it's a log, for the most part, perhaps an XML solution is in order.


Is a non-relational database like CouchDb an option? I have no experience with it, but from what I've read/heard about it, it may work well for you.

You could possibly get away with just storing this:


  • Id
  • ActionTypeId
  • SessionId
  • TimeStamp
  • DataObject


The standard answer would be to put the data values into a separate table, with the Id from the Action table as a foreign key into the data table. I.e. an Action would look like:

Id  ActionTypeId  SessionId  TimeStamp  

Then you'd have a Data table that would look something like:

ActionId  DataType  DataValue  


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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »