Results 1 to 2 of 2
Thread: activity & table strategy
Feb 12, 2005, 08:18 #1
- Join Date
- May 2002
- 0 Post(s)
- 0 Thread(s)
activity & table strategy
I have three tables that registered users insert to.
The 1st table contains the primary id associated with username/password/address and general info. (though incomplete)
first question: the main production table (not the "1st table) contains almost all foreign keys. With he primary id mentioned right above being carried onto this main table -- should I index this main production table on this primary Id (would I be risking referential integrity?) Reason is to use the 1st table only for initial inserts for new registrants) and by doing this turn three way joins into two way, and four-ways into three-ways -- resulting in less processing required throughout.
second question: the three tables mentioned at the top are being inserted to by first time users (one of the three tables, the "1st" table" is being checked against constantly for username/password, etc). Is it a good idea for a site that is expected to be a high traffic site (SELECT statements/user updates/new inserts) to have these values split off into different table types:
"initial tables" - in this case, the "three tables" where users only register for the first time, almost a staging technique. One of these tables (1st table) contains username/password. Values for "production" and "update" (first row only) are pulled from these"initial tables.
"update tables" - to eliminate the possibility of users performing updates and insert simultaneously and to record histories.
"production tables" - where only queries are run. columns indexed.
Reason being to allocate processing of transactions seperate from queries.
These three tables contain somtimes duplicate values but are used in a definite order sequence. (more work on the admins part obviously!)
Last edited by datadriven; Feb 12, 2005 at 09:12.
Feb 12, 2005, 12:28 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 52 Post(s)
- 2 Thread(s)