SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    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 10:12.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    none of that really makes sense to me without seeing the table layouts, primary/foreign keys, and defined indexes

    any reason why you don't have just one table? that would eliminate the redundancy, you know

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •