4

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?

Boris Callens
  • 90,659
  • 85
  • 207
  • 305

7 Answers7

7

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 ...
akuhn
  • 27,477
  • 2
  • 76
  • 91
4

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.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • We use the XML solution here for our application log tables and it works well. I'm a relational purist for the most part, but this is an exception that I think is good - it's not outward facing and utilization of the table tends to be a well known subset of uses. You can use xquery to get at specific items if you really need to do that in a query. – Tom H Dec 15 '09 at 16:54
2

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

ActionID
Property
Value
Rik
  • 28,507
  • 14
  • 48
  • 67
2

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.

DA.
  • 39,848
  • 49
  • 150
  • 213
  • Actually, I don't think you'd even need 3 tables. 2 should work. Just give the Log table a reference to the ActionID – DA. Dec 15 '09 at 16:42
1

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
Austin Salonen
  • 49,173
  • 15
  • 109
  • 139
  • I like that you're thinking out of the box, but I think since this will be a system consumable by many different kinds of projects in many different kinds of languages, supported by many different kind of programmers that I would like to keep it oldskool ;) – Boris Callens Dec 16 '09 at 08:08
0

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
Jerry Coffin
  • 476,176
  • 80
  • 629
  • 1,111
0

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

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71