Re: representing a sequence of events in a database

Thu, 12 Aug 2004 07:53:07 -0700

The problem you point out is one of the reasons this isn't the best way to organize your data. A better way would be to keep your events table:

 events
  event_id
  event_name
  event_description

but properly normalize the sequences table by putting one event per row like this:

  table sequences
    sequence_id
    position
    event_id

Make (sequence_id, position) a primary or unique key. Then if sequence 1 is e_1, e_5, e_3 and sequence 2 is e_7, e_4, you would have

  events
    1  'e_1'  'description of event 1'
    2  'e_2'  'description of event 2'
    3  'e_3'  'description of event 3'
    ...

  sequences
    1  1  1
    1  2  5
    1  3  3
    2  1  7
    2  2  4

and so on.

Now it is easy to anser your question, "In what sequence has event e_5 happened as the third event?"

  SELECT s.sequenceid
  FROM sequences s JOIN events e ON s.event_id = e.event_id
  WHERE s.position = 3 AND e.event_name = 'e_5';

Michael


Alexander Hannemann wrote:

Hi,

I have the following problem:

I want to store a list of events e_1 to e_n in one table, and in a second
table I would like to store sequences of these events so that later I can
ask questions like: In what sequence has event e_5 happened as the third
event. My more or less trivial solution would be to have an events table

table events
event_key
description of event

and a sequence table
table sequences
sequenceid
numberofevents
event_key1
event_key2
event_key3
...
event_keyN
,
with some predefined maximum number of events. Lets say N_max =10 Now if
a sequence happens to contains only 3 events. How do I calculate the
'correct', normalizable sequenceID. One way would be a string
concatenation of the event keys. to define as the primary key.

Any hints

Thanks
Alex




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to