StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODesign for simple database for an event stream
text
Body
copied!<p>For a personal project, I want to record a stream of unrelated events, to gather simple statistics on later. Each event can have [wildly] different data associated with it.</p> <p>I'm looking for some advice on how to structure this. As this is a small project, my main goals are:</p> <ul> <li>Ease of setup</li> <li>Ease of use (i.e. no strange joins)</li> </ul> <p>Performance isn't critical, and I will be the only user.</p> <p>To give you an idea of what I'm aiming for, here is my current plan:</p> <blockquote> <p>Table: <strong>Event_Definitions</strong></p> <p>Columns:</p> <ul> <li>ID</li> <li>Name</li> <li>Type</li> <li>Options</li> </ul> <p>Table: <strong>Events</strong></p> <p>Columns:</p> <ul> <li>ID</li> <li>Definition_ID</li> <li>Option_Values</li> <li>Notes</li> </ul> </blockquote> <p>So say we have two event definitions, like this:</p> <blockquote> <p>ID: 0; Name: Pigeon_Released; Type: Time; Options: null</p> <p>ID: 1; Name: Fed_Pigeon; Type: Fixed_List; Options: bread,crackers,tofu</p> </blockquote> <p>Then we log some events:</p> <blockquote> <p>ID: 0; Definition_ID: 1; Option_Values: bread; Notes: null</p> <p>ID: 1; Definition_ID: 1; Option_Values: tofu; Notes: "he cooed"</p> <p>ID: 2; Definition_ID: 0; Option_Values: 12:34:56; Notes: "I cooed too"</p> </blockquote> <p>The option values will be enforced through the program.</p> <hr> <p>After a number of events have been collected, I will be collecting together events of the same type for comparison. I don't expect to be retrieving events with specific values, just events of the same type.</p> <hr> <p>So the question is, again, any advice or comments on this strategy or alternatives? I appreciate how simple and straightforward this approach is, but it bothers me that even though the 'values' for an even could be strings, times, index numbers, etc. etc., they all get stored in the same column.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload