Table with unknown number of columns

Question

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?

Answer 1

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

ActionID
Property
Value

Answer 2

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 ...

Answer 3

Given your constraints, I think you have come to the best solution available.

In the worst case, you can always add more fields, and existing apps shouldn't notice, and the new apps can take advantage of them.

This answer assumes the system is currently designed as in the question, and the OP is asking for best option to expand the system w/min impact. A new system should clearly be designed as in the other answer posts.

Answer 4

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

Answer 5

Add more tables and set up relations

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

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

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

or

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

Answer 6

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.

Answer 7

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:

Actions

• Id
• ActionTypeId
• SessionId
• TimeStamp
• DataObject

Answer 8

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