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.
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?
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?
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?
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?
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.