For any personal project, I wish to record a stream of unrelated occasions, to collect simple statistics on later. Each event might have [extremely] different data connected by using it.

I am searching for top tips regarding how to structure this. Because this is a little project, my primary goals are:

  • Easy setup
  • Simplicity of use (i.e. no strange joins)

Performance is not critical, and I'll be the only real user.

To provide you with a concept of what I am striving for, here's my current plan:

Table: Event_Definitions

Posts:

  • ID
  • Title
  • Type
  • Options

Table: Occasions

Posts:

  • ID
  • Definition_ID
  • Option_Values
  • Notes

So say we've two event definitions, such as this:

ID: Title: Pigeon_Launched Type: Time Options: null

ID: 1 Title: Given_Pigeon Type: Fixed_List Options: bread,crackers,tofu

Only then do we log some occasions:

ID: Definition_ID: 1 Option_Values: bread Notes: null

ID: 1 Definition_ID: 1 Option_Values: tofu Notes: "he cooed"

ID: 2 Definition_ID: Option_Values: 12:34:56 Notes: "I cooed too"

The choice values is going to be enforced with the program.


After numerous occasions happen to be collected, I'll be collecting together occasions of the identical type to compare. I do not be prepared to be locating occasions with specific values, just occasions of the identical type.


So now you ask ,, again, any advice or comments about this strategy or options? I appreciate how easy and straightforward this method is, however it bothers me that despite the fact that the 'values' to have an even might be strings, occasions, index amounts, etc. etc., all of them get saved within the same column.

Effectively there is a "blob" of information that may in principle hold anything, after which a schema definition to express the way the blob ought to be construed

it's a date

or

it's one of these values

This really is quite cost effective for an individual project. A problem with the "blob" approach is the fact that there might be something of the impedence mismatch when you are performing queries. If you want to use the blob contents it will likely be just a little cumbersome.

Another comment is your defintion table might become limited, you need to put more sophisticated, structured data inside your blob.

I question if using XML as the blob helps. The XML schemas then define the blob contents, maybe you do not need your event definition table whatsoever. Does your database (as some do) have XML abilities you can exploit?

An RDBMS isn't the best technology solution for a celebration stream, as well as your needs for variable characteristics doesn't fit the relational paradigm.

Use a Message Queue rather.


Okay, if you possess the have to store and compare occasions, that's different. After I hear "event" I suppose it is simply legitimate-time notification. So my suggestion of the Message Queue might not be appropriate within this situation.

Still, relational databases don't support variable characteristics inside a table easily. You can test using design designs like Concrete Table Inheritance or Class Table Inheritance to resolve your condition.