[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