CODEWITHSUNDEEP
sqldatabase-design
Boris Callens
Boris Callens

Reputation: 93297

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?

Upvotes: 4

Views: 2050

Answers (7)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

Upvotes: 0

Austin Salonen
Austin Salonen

Reputation: 50215

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

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

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.

Upvotes: 4

DA.
DA.

Reputation: 40663

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.

Upvotes: 2

Jerry Coffin
Jerry Coffin

Reputation: 490048

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

Upvotes: 0

akuhn
akuhn

Reputation: 27793

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

Upvotes: 7

Rik
Rik

Reputation: 29243

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

ActionID
Property
Value

Upvotes: 2

Related Questions