1

Couldn't find a newbie forum; hope this is in the right place.

In my quest to learn more about databases, analytics, and creating service APIs to work with them, I decided to scrape an online archive of Jeopardy data and plan to reconstruct the data into a functional db. Pretty sure this has been done before, but learning is the primary objective here :)

I'd like to get the data structured so I can run some advanced statistics on it. To do so, I'll need to query things like a player's score at any given point in the game, see who picked which clues and in what order, identify (and grade) players' wagering on daily doubles & Final Jeopardy, etc. If all goes to plan, it would be neat to expose this via public API so others can play with it too.

There's a decent amount of data here, though it's not very big compared to enterprise dbs:

  • 6000 distinct games
  • ~60 clues per game, so ~360k total unique clues.
  • 12000 players, which includes duplicates (ex: Ken1, Ken2, Ken3)

Below is the latest draft of my data model (original SVG, updated SVG) based on the data available from j-archive, the general structure of Jeopardy games, and my limited understanding of database design. Appreciate any feedback -- especially on the clue_response table, which is the only table with transactional data.

png of the updated data model

Data is currently stored in an sqlite3 db. Will probably migrate it to MySQL once I settle on a structure.

closed as unclear what you're asking by Erik Darling, RDFozz, SqlWorldWide, McNets, LowlyDBA Jan 25 '18 at 20:24

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Your FK's say things like "player_id" and "clue_id" on the remote side; use that SAME name in the table it's a primary key for as well, i.e. player.player_id, not just player.id. It'll make joins more obvious and prevent dumb mistakes like accidntially joining clue.id 5 to player_id 5. – Anti-weakpasswords Jan 24 '18 at 4:20
  • Load your diagram into Stack Overflow for direct display here. – Basil Bourque Jan 24 '18 at 7:13
  • 1
    I assume this is personal preference? The primary db I use at work spells out the PK_id every time, while the db I use second most uses [table].id. I personally find the [table].id method easier for PKs. I can see it being confusing if your SQL style puts the table join parameters in the WHERE clause instead of in-line with the JOINs. I do the latter: JOIN player ON player.id = clue.player_id – brystmar Jan 24 '18 at 7:13
  • Changed the link to an image, @BasilBourque. Couldn't figure out how to attach it as a file, and stackexchange doesn't natively support embedding SVGs as images. – brystmar Jan 24 '18 at 8:05
  • 1
    Perhaps you could direct us to areas you're uncomfortable with? It's helpful if your question has an actual, clearly stated question in it. – RDFozz Jan 24 '18 at 16:29
2

Having numbered columns is an anti pattern. Your Game table shows it.

The relationship between Game and Player is many-to-many, even though the cardinality is fixed from a game's perspective. Resolve the problem by adding a new table ("Participant"?), with foreign keys to both Game and Player.

To see how it helps, try writing a query to count the number of games an individual was in using each design.

The relationships to Player Round Result should be to this new table, not to Game and Player separately. This ensures a Player must necessarily have been in a Game to have a result recorded. The foreign key columns in Player Round Result will have the same names and types, but the foreign key constraint declaration will reference a different target.

Further, I'd suggest the table currently called Player would be better as Person, since the values it holds are for the human per se. The new table I suggest above would be well-named as Player as it is all about a person participating in a game.

  • After some mental debate, I think I agree with you. Updated model. Moved occupation & hometown under 'player' since those are unique to a matched player-game pair. Is there a more effective way of dealing with aliases? – brystmar Jan 24 '18 at 18:34
  • Is it important to know in which game the alias was used? If not link alias to Person, not Player. Can an individual have more than 1 alias in a game? No - make the alias an attribute of Player. – Michael Green Jan 31 '18 at 9:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.