We are also looking into alternatives for compatibility that require less updates and/or less databases. Our first idea was a user by user table, so 1 possibly 1000000by1000000 table. But Eric mentioned that adding columns can be very time consuming. The obvious alternative is a table with 3 columns, userA, userB and score. This would be a 1000000*1000000by3 table, which requires no column adds, but the 1000001st user will require 1000000 new rows to be added. The last idea we thought of was having a separate table for each user. So 1000000 1000000by2 tables. Not sure right now which one we are going to go with, or if there is a better option.
Subscribe to:
Post Comments (Atom)
The general attitude with RDBMSes (like MySQL) is that nothing the users do should cause any "DDL" (database definition language) to be executed. That means no new tables, no new columns, no new databases, no altered columns, etc. Your schema should be able to accomodate everything triggered by users without changes.
ReplyDeleteYou don't actually have to add those 1000000 new rows if they're all zero -- just assume that if you don't find the row you're looking for, then it's zero or calculate it right there.
According to Donald Knuth: "Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%."