MySQL DB Set Up Questions

[MediaOhgr]

Hey There! I'm looking for some advice on a database I'm setting up. What I've done is set up a MySQL db on my hard drive using PHPTriad then I've installed MyODBC so I can administer with MS Access.

The database is a hockey pool that I've started but I'm having some problems. Here's what the db looks like thus far:

Table 1 - entryInfo:
- entryID
- lastName
- firstName
- password
- email

Table 2 - playerInfo:
- playerID
- lastName
- firstName

Table 3 - goalieInfo:
- goalieID
- lastName
- firstName

Table 4 - playerStats:
- playerID
- goals
- assists

Table 5 - goalieStats:
- goalieID
- wins
- shutOuts

Table 6 - playerTeams:
- entryID
- playerID

Table 7 - goalieTeams:
- entryID
- goalieID

Using relationships in Access, I've been able to generate some pretty intense queries to pull the info out that I need. No problem there. My problem is conceptualizing how I'm going to add the player and goalie points up, then input that to the database so I can have queries that look at the teams, and then sort the standings decending in order so I can have a stats column on the page.

Make sense? Right now I don't have a totalPoints column in the player and goalieStats tables. I think I'll need one though to sort. Question being, how do I get that to update when I update the goals/assists and wins/shutouts? Any advice will be greatly appreciated.

[Marshall]

Using relationships in Access, I've been able to generate some pretty intense queries to pull the info out that I need.

The SQL queries that you develop in MS Access will not necessarily work with MySQL -- anything involving nested select statements, for instance.

Question being, how do I get that to update when I update the goals/assists and wins/shutouts?

MySQL does not support triggers. You'd need to use explicit SQL calls in your PHP(?) source code to update this information.

Will you have multiple playerStats entries for each playerID and/or multiple goalieStats entries for each goalieID? If not, then there's no point in having separate tables for this data. You really only need 5 tables:

Table 1 - entryInfo:
- entryID
- lastName
- firstName
- password
- email

Table 2 - playerInfo:
- playerID
- lastName
- firstName
- goals
- assists

Table 3 - goalieInfo:
- goalieID
- lastName
- firstName
- wins
- shutOuts

Table 4 - playerTeams:
- entryID
- playerID

Table 5 - goalieTeams:
- entryID
- goalieID

You don't necessarily need fields for storing totals values either. You have the individual values -- goals, assists, wins, shutOuts -- you can use the SQL SUM() function to add these values for any particular user when you need them.

- Marshall

[MediaOhgr]

Thanks Marshall! Not only did you answer my question, but you added some extra info!

Yeah, I know that the SQL statements may not work with MySQL. I test them all locally before putting them on the web. To this point what I've need done is working fine. So far so good!

Thanks!