I'm trying to wrap my head around a database that will maintain 'box scores' for sporting events and be searchable on a various amount of fields including how a player performs home and away and in wins losses.
I can't figure out how to design the tables properly for the home/away win loss scenario in a way that normalizes. Any suggestions would be appreciated.
I'm not a sports person, so I'm not sure this will track everything you need it to track, but it should give you a start:
Location: ID, Location Name
Team: ID, Team Name, Home Location (location foreign key)
Player: ID, Team ID, Player Name
Game: ID, Location ID, Game Name
Stat Type: ID, Name of Stat (Runs, At Bat, RBI, etc)
Stat: ID, Game ID, Player ID, Stat value, Stat Type
Does that help?
Unfortunately I don't think it does, it's the home away winner loser thing i want to make easier so that when i query the database i only have to query one field. I might have to make a separate table fore 'home/away' related to the 'games' table with a key relationiship on the UID for the game number.
I was thinking you could create a query that determines the home/away by joining the Stat table to the Game table, which has the Location Id. Then join the Stat table to the Player table and join Player table to the Team table, which has the Home Location Id. I would then compare the Home Location Id to the Location Id in the Game table to see if the stat is for a home game or an away game.
Of course, you can create a field in the game table that tracks if the game is home or away, but I wouldn't recommend it. The query would be easier to put together, but the database would not have proper normalization. Having said that, the method you mentioned would still work though.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.