[Newbie Question] Database Design Basics help

[eaglestrike7339]

Ok, well, I want to make a database backed website that uses php and mysql to record times for various cross country and track races.

Unfortunately, I really have no experience in this field, but I have borrowed and read a php+mysql 4 dummies guides, so assume basic knowlege, if that.

 

Right now, In DB timekeeper, I have tables

 

tk_runners

        runner_id (rid) (primary key)(automatically generated)

        firstName

        lastName

        school

        description (running style, injury reports, etc)

        classOf (graduation year, to be used to determine Fr/So/Ju/Senior)

 

tk_races  //each individual race within a meet, so the frosh soph 2 mile, or the varsity 3 mile

        meet_id

        event_id  (primary key, i guess)

        totalRunners

 

tk_eventPlaces  //i would have 1 row for each runner in the race should I append a meet_id var to the [end]                    // and have a seperate eventplaces table per race?

        event_id (used once per each place)

        runner_id (could be indexed/cross references when I make a "My races" kind of page

        time (is there a var-type that can do milliseconds? This would be needed for recording time for sprints)

        place (auto incremented, reset to 0 if event_id[cell] != event_id [cell-1])

       

 

tk_meets //record locations of the meet itself, like an address, phone number, school that was in charge, etc

        meet_id (primary key)

        address

        date

        conditions (like, if meet was crappy weather, or perfect to have a personal record time)

 

 

 

waaay down the road, I would also like to work out a way to parse a text file into a database. Right now, we get our results like this.....(sorry for the link, but anyone here would be disgusted to see how unorganized an unuseful this is)

 

here is a sampler: (http://il.dyestat.com//?pg=dyestatil-2008-Cross-Country-Sectional--Schaumburg-1108&PHPSESSID=bd9da159739c9e221accb51495b3e003)

SCHAUMBURG IHSA SECTIONAL CROSS COUNTRY MEET

                        SATURDAY, NOVEMBER 1, 2008

 

----------------------------------------------------------------------------

                        GIRL'S INDIVIDUAL RESULTS

----------------------------------------------------------------------------

 

Place TmPl No.  Name                          Event Yr School                Time   

===== ==== ==== ============================== ===== == ====================== ========

    1    1  106 OLSON, HEATHER                TEAM  12 WARREN                17:22.65

    2    2  22 SCHMIDT, MELISSA              TEAM  11 HOFFMAN ESTATES        17:25.10

    3    3  29 McINTOSH, SARAH                TEAM  10 PALATINE              17:25.85

    4    4  71 JONES, OLIVIA                  TEAM  9  BUFFALO GROVE          17:37.50

    5    5  36 WHITE, ANNETTE                TEAM  11 PROSPECT              17:42.95

    6    6  50 GASTFIELD, CHRISTINA          TEAM  10 ROLLING MEADOWS        17:48.05

    7    7    2 PETREY, BRITTEN                TEAM  10 SCHAUMBURG            17:49.60

    8    8  43 MARTIN, ASHLEY                TEAM  11 HERSEY                17:52.75

    9    9    1 FALSEY, COLETTE                TEAM  10 SCHAUMBURG            17:54.30

  10  10  44 CLOHISY, SALENA                TEAM  10 HERSEY                17:59.15

  11  11  23 WORMAN, HANNAH                TEAM  11 HOFFMAN ESTATES        18:02.40

 

 

Thank to any and All that help me out,

 

eagle

 

 

[eaglestrike7339]

Mod, does this need to be moved to Application Design?

If so , I apologize, next time i'll spend the extra second poking around before i get to posting.

 

 

ps. On top of that, I did not see an edit button, so I posted here. Sorry in advance for my ignorance.

[tarlejh]

So what is the question?  Data model looks OK, but you could spend about a year learning only the basics of database and application design (so I'm afraid you'll need to be a bit more specific as to where you're having trouble).

 

There are undoubtedly better developers lurking that could reply to this - but if I were coding it:

 

In general, you divide the application into some number of abstraction layers, or what is known as an n-tier design.

 

For example,

• 
  runnerview.php
    This page is html.  Should be no PHP in this page, except perhaps iterating through arrays or objects.  To strictly separate PHP and HTML, you might use placeholders for dynamic values ( e.g. {value} ), leaving only a HTML template.
   
  
• controller.php
    This page is in charge of making the view responsive to user input.  To put it another way, it would accept an argument (as some sort of input) for some sort of parameter and hand it off to a database, (e.g. generate a page based on the runner's last name.)
   
  
• model.php
    This page would be in charge of setting and getting data from a database - looking up a runner's result, adding deleting editing runners, etc.  It communicates with the database and returns a resultset or an object.  This is the only part of the application allowed to address the database directly.  Look in the manual for PDO or use a third party ORM like Propel (google it if you've never heard of this).
  

 

Now whether this is the best way to design an app is open to to debate, but it should get you started.

 

 

 

 

 

 

 

 

[eaglestrike7339]

i guess my real question is whether my databases look well-designed or not.,

Also, i heard about indexing. Should I look at trying to index the race times by runner_id number?

 

again, thanks

[Barand]

text parsing

<?php
$data = array(
'    1    1  106 OLSON, HEATHER                 TEAM  12 WARREN                 17:22.65',
'    2    2   22 SCHMIDT, MELISSA               TEAM  11 HOFFMAN ESTATES        17:25.10',
'    3    3   29 McINTOSH, SARAH                TEAM  10 PALATINE               17:25.85'
);

function myfunc($a) {return trim(str_replace('.', ' ', $a));}

$result = array();
foreach ($data as $line)
{
    $line = str_replace (' ', '.', $line);
    $ar = sscanf($line,'%5s%5s%5s%32s%4s%4s%24s%8s');
    $result[] = array_map('myfunc', $ar);
}

echo '<pre>', print_r($result, true), '</pre>';
?>

 

Place and total_runners can both be calculated, so no need to store.

 

Yes, index eventid and runnerid in the places table

[eaglestrike7339]

Terrific! Thanks soo much for the code.

 

Final question, though: will generating all the place values show the website load time down? Like, if I ran 20 races, and it had to calculate places, rankings, arranging races from fastest to slowest, comparing race times with other runners, Would it noticeably slow down the page load time, or put extra stress on the database?

 

Thanks again,

eagle

[Barand]

Depends on the type of queries that you will need. If you are just doing race results then order by time  with a simple counter will show the placing.

 

The sort of query that would impose is load is "list all races where runner X was placed 4th or lower"