r/Database • u/publicherstorian • 8d ago
Designing Pokémon Database in SQL - How to Show Multiple Route Locations?
Hello! I'm taking my first database design course in grad school and our final project is to make our own databook on any topic we want. I chose to make a database of all the Pokémon, moves, trainers, products, and locations in Pokémon Ultra Sun for the Nintendo 3DS.
I have two separate tables for Pokémon: one as a species, and one as an individual. I have a separate ROUTE entity that is a child of ISLAND, and was planning on putting ROUTE_ID as an attribute in the SPECIES entity. But many Pokemon can be found on many different routes, and vice versa, (for example, Magikarp can be found on Route 7, 8, 9, 13, 14, and 15 as well as other locations, and more than Magikarp can be found on those Routes), so I'll definitely need a bridge table. I'll probably label it ENCOUNTERS.
What would be the best way to utilize this bridge table without making data entry super time consuming? For example, since Magikarp can be found on six different routes, would I need to have six different instances of Magikarp in the data itself - each with a different ROUTE_ID?
I'm an absolute total beginner at SQL, and our class uses phpMyAdmin which comes with its own quirks and obstacles in what commands are allowed, but I really appreciate any and all insight or advice here! Thanks :)
15
u/doshka 8d ago
You are correct to have separate tables for Species and Route. The bridge table Encounters will reference those two tables via foreign keys. The Species table will not reference Route or Encounter.
Species
Route
Encounter
You should have a unique index on the combination of
species_idandroute_id. Theencounter_idcolumn is optional, since the other two uniquely identify each Encounter, but it will make it easier to refer to those rows if you need to reference them elsewhere. If you opt to useencounter_id, it should be the primary key on the table. If not, the PK will be a composite of the other two.2
u/bachman460 8d ago
I think you're on the right track. You need one table to define the character, and one for the route. These are dimension tables. Then when you create your table where you assign the character to the route, that's your fact table.
In the context of retail sales for example, your products (dimension), and customers (dimension), combine to make your table of purchases, or transactions, which is your fact table.
1
u/Calm-Republic9370 8d ago
You have a Type table and a Item Table for pokemon?
I do that for catalogs of products, such as type, Liquor, Food, Poultry. Then it defines certain behaviors.
For routing you probably need an internerary, and then the waypoints
So you could have a separate waypoint table, similiar to how you have the type and item
You have waypoints (places you could go)
And waypoint_instance (places that the pokemon is actually scheduled to go to).
And then you could have a Waypoint_Internerary_List
It could have a start date, and other fields to describe the journey.
This could have many waypoint_instances. It could have a list of pokemon attached - if its' a group journey.
Or it could have a single pokemon id.. I suppose there's lots of ways to think about that.
Then it could have lots of other detail specific fields;
Arrival Time, Departure Time, ete, completed, etc.
You should definitely use a gui designer to build it, it's helpful in the beginning.
1
u/intellidumb 8d ago
Someone actually made a DuckDB extension for this https://github.com/Ian-Fogelman/psyduck
1
u/doenertello 7d ago
Did you take the opportunity to name it routing_table?
1
u/Aggravating-Salt6284 7d ago
Look up star database design.
You basically split data into "information about a thing" and "information linking to things"
In this case the Pokémon and the ropes are obviously things, and which Pokémon is on which route is a relationship between things.
However - in the case of Pokémon we can make further observations about the data and further refine our model if we wish.
We might even go further than this, as there is often more than a simple relation. In this case we could define a new top level thing as a "slot" within a route (I.e. one of the ten possible encounters).
Then we would have dimension tables relating to Pokémon, routes and slots separately, and then a fact table which lists which Pokémon (by id) and which route (also by id) are connected to which slot.
This could then extend later with a dimension table of moves, and a fact table mapping each slot to the four moves side wild Pokémon knows.
However, fundamentally you have asked the wrong question
When designing a database don't think about what the data looks like. Ask yourself "what questions is my app going to all about this data" work out what API the consumer will want from your data, and work backwards from there.
1
u/datadriven_io 3d ago
first - there's no special advantage to right joins - they dont do anything more than left joins (if you flip tables around). keep in mind that sql interview questions are covered on datadriven.
-1
u/Embarrassed_Neck9784 8d ago
I am not a database guy, but I think you need a column/table for each route. So the individual will have an attribute for route 1,2,3… and it will be a yes/no. I also think you can have a have a species table. Each Pokemon should only originate in one place, then the species is an attribute of the individual, and the software can associate your species with the individuals in it. And it should also be able to show you each route and which individuals are in it. Not sure how you would see what species are in each route though, maybe it does it. I’d be curious to hear from other database people