I've got a little bit of a design question going on here. I have my own ListServ implementation written in Python, that works with a Postfix+Dovecot system for handling mail, and a PostgreSQL database backend for most of the functionality for determining the following:
- What lists exist on the listserv (the
liststable in the diagram below)
- Who has ever been seen on the listserv as a member of any list. (the
memberstable in the diagram below)
- Who is a current member of which lists, and: (the
listserv_membershiptable in the diagram below, linking individual 'members' to the individual 'list' they are a member of by listserv ID and member ID)
- Can they send to the list? (hidden field in the table)
- Can they receive messages sent to the list? (hidden field in the table)
- ListServ statistics - Message counts for each day, based off the current date's timestamp, for each individual list that has had activity. (the
Currently, the structure of the DB looks like this (Note that I have blanked out all the non-relevant fields in tables I'm not going to be focusing on, and leaving the primary keys in place; the relevant table is marked with three white asterisks next to it, and this diagram is created with DataGrip):
Please don't call me out about diagonal arrows in the diagram - this one was done as a quick and dirty diagram, while I have a good ERD done by hand in Visio, which is unfortunately not available on the computer I'm on now
The specific table I'm going to ask about is the
listserv_stats table here, not the rest of the design of the database.
Now, initially, I was only worried about tracking statistics over a day-to-day basis and 'all time' very rough daily average based on how many days the listserv has had activity. I am now a lot more concerned about other statistics, such as monthly averages, month-to-date average, year-to-date average, and yearly averages, so I'm considering a redesign here to better accommodate such searches.
The current table
CREATE statement is this:
CREATE TABLE listserv_stats ( lsid INTEGER NOT NULL, datestamp DATE DEFAULT now() NOT NULL, msg_count INTEGER NOT NULL, CONSTRAINT listserv_stats_lsid_date_pk PRIMARY KEY (lsid, datestamp), CONSTRAINT listserv_stats_lists_lsid_fk FOREIGN KEY (lsid) REFERENCES lists (lsid) ); COMMENT ON COLUMN listserv_stats.lsid IS 'ListServ ID'; COMMENT ON COLUMN listserv_stats.datestamp IS 'DateStamp'; COMMENT ON COLUMN listserv_stats.msg_count IS 'Message Count';
What I'm thinking of doing is expanding the table to replace the
datestamp column as follows. This would allow me to better handle month-to-date, monthly historic, year-to-date, and yearly historic values, in my opinion, in my Python code (which can reconstruct the individual values into datestamps):
CREATE TABLE listserv_stats ( lsid INTEGER NOT NULL, year INTEGER NOT NULL, month INTEGER NOT NULL, day INTEGER NOT NULL, msg_count INTEGER NOT NULL, CONSTRAINT listserv_stats_lsid_date_pk PRIMARY KEY (lsid, year, month, day), CONSTRAINT listserv_stats_lists_lsid_fk FOREIGN KEY (lsid) REFERENCES lists (lsid) ); COMMENT ON COLUMN listserv_stats.lsid IS 'ListServ ID'; COMMENT ON COLUMN listserv_stats.year IS 'Date: Year'; COMMENT ON COLUMN listserv_stats.month IS 'Date: Month'; COMMENT ON COLUMN listserv_stats.day IS 'Date: Day'; COMMENT ON COLUMN listserv_stats.msg_count IS 'Message Count';
From this, I can restructure the existing predefined routines in the DB to allow for my Python code to call "New Date Record" and "Update Date Record" functions by passing the entire date as values.
My question is, is this design change a good idea, or can I achieve everything with the original "datestamp" table I already have in place? And should the original design be kept, I will have to figure out how to structure the routines to provide the data for those specific date ranges from the system (I'll post on SO for that, most likely).