[thelist] Doing things just once -- tracking in database.
r937
rudy at r937.com
Tue May 26 16:53:01 CDT 2009
> I'm leaning toward an event table with columns "account_id",
> "event_id", and "processed_time"
this is a pretty decent design, and yet very simple, so i would go with this
until you get further requirements that don't fit it
make the primary key a composite of all three columns, and then optionally
add two additional indexes, as follows:
CREATE TABLE account_events
( account_id INTEGER NOT NULL
, FOREIGN KEY ( account_id ) REFERENCES accounts ( id )
, event_id INTEGER NOT NULL
, FOREIGN KEY ( event_id ) REFERENCES events ( id )
, processed_time DATETIME NOT NULL
, PRIMARY KEY ( account_id, event_id, processed_time )
, INDEX reversi_1 ( event_id, account_id, processed_time )
, INDEX reversi_2 ( event_id, processed_time, account_id )
);
whether you need both additional indexes depends on which queries you plan
to run regularly
the PK satisfies all event queries for a specific account
the other indexes satisfy queries for specific events by account or by
processed time
the nice part about all three (PK plus the other indexes) is that they are
each a covering index, so when used in a query, the optimizer doesn't ahve
to access the actual rows of this table at all
;o)
rudy
http://simply-sql.com/
More information about the thelist
mailing list