Results 1 to 7 of 7

Thread: Is this 3NF?

  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Is this 3NF?

    I've created an SQL database design, and I need to make sure if it's properly normalized and efficient. I'm attaching a flow chart, so let me know if it looks good.

    Question 1)
    I'm creating software to manage a debate tournament. Every time a student participates in an event, her participation is recorded in the "student round log" table (see diagram below). This just has one primary and a few foreign keys, which link to general information about students, what the tournament was like, what happened in the particular debate event, and what kind of school she goes to. I'll then take these tables and generate all the information you can imagine--like creating wins/losses statistics, brackets, etc. Is this an efficient way to arrange my database?

    Click image for larger version. 

Name:	database format.JPG 
Views:	352 
Size:	40.7 KB 
ID:	9152

    Question 2)
    Okay, say for 3 years the program manages 10 tournaments a week, each with 200 students, who each participate in an average of 10 events. That's about 3,000,000 entries in the big "student round info" table at the bottom. It seems wasteful to constantly be searching through records that are a few years old, that you only need occasionally. Should I optimize somehow? If so, would it make the code more cumbersome? What factors should I consider?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by djslothario
    Question 1)
    Is this an efficient way to arrange my database?
    StudentRoundLog has student_id twice, but other than that, yes

    Quote Originally Posted by djslothario
    Question 2)
    Should I optimize somehow? If so, would it make the code more cumbersome?
    yes, optimize by assigning indexes, and no, there is no effect on the code at all



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    4
    Thanks. Also--pardon my n00bish questions, I'm learning this all through online tutorials--I can create new records for students and tournaments without having an event associated with them yet, right? For instance, someone would first register a tournament, then register students, and then only later would students participate in events. Something I read vaguely gave me the impression that it's not a good idea, though. I can do that safely, right?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by djslothario
    I can do that safely, right?
    yeppir
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    One more thing. This has one big table at the bottom that records every single round an individual student takes part in, but there are certain types of round--say LD debate, CX debate, extemporaneous speaking. Should we split that big table at the bottom into each event type? What are the benefits/drawbacks of doing that?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how are the different event types different? what different data values does any one event type have from other event types?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2009
    Posts
    4
    Actually, the events are a little different, so I'm going to make a table for each one of them. For instance, extemporaneous speaking is more based on individual ranking, so I'm going to make one entry in the extemp table per student per event, but since, say, LD debate is always made up of two students, there's going to be one entry per debate.

    I think I have a pretty clear idea of what I'm doing now. Thanks for your help. If you see anything else, though, please point it out.

Posting Permissions

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