Will this DB design work for updating specific rec

Results 1 to 2 of 2

Thread: Will this DB design work for updating specific rec

  1. #1
    Join Date
    Dec 1969

    Default Will this DB design work for updating specific rec

    hey all,<BR><BR>here&#039;s my dilema. i&#039;ve got a table, called &#039;records&#039; that keeps track of different race statistics for a specific user. here are the fields:<BR><BR>statID<BR>*id - this is linked to a table with usernames<BR>racedate<BR>racetrack<BR>*raceclass <BR>finishposition<BR>*finishpoints<BR>laptime<BR> <BR>* - these are fields that need to be reused.<BR><BR>.. now i have another table, called &#039;stats&#039;, which keeps track of overall points for each race class and the number of wins also. here are the fields:<BR><BR>sID<BR>id - link to user table<BR>class1<BR>class2<BR>class3 .. all the way to class12<BR>overallpoints<BR>wins<BR>toptenfinishes <BR><BR>.. does this look feasible? right now, the records table works fine. however, i&#039;m not sure how to set up everything so that the stats table gets updated everytime a record in the records table is added/deleted or modified.<BR><BR>i thought about having a function try and do this, but that seems like it could get pretty messy.<BR><BR>essentially, there will be one record per rider in the stats table, which displays all their respective stats. <BR><BR>if anyone could lend some advice on the best way to crank this out, i&#039;d greatly appreciate it.<BR><BR>thanks,<BR><BR>ed<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Will this DB design work for updating specific

    I don&#039;t have a whole lot of time, but I don&#039;t think you need to have a second table, all the data can be retrieved from the records table, can&#039;t it? If so, don&#039;t create a second table is the good thing to do from a &#039;normalization&#039; point of view.<BR><BR>The only reason you would want to create a second (agregate) table is that create the stats table on the fly would take way too long. <BR><BR>If this is for a SQL Server database you should look into triggers, if this is for Access you would have to do it manually. (After every insert in the records table, update the stats table e.g. UPDATE Stats SET overallpoint = overallpoints + 10 WHERE UserID = 123 etc. etc.)

Posting Permissions

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