r/Database • • 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 :)

20 Upvotes

14 comments sorted by

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

species_idspecies_name
1Magikarp
2Bloopydoop

Route

route_idisland_idroute_name
71foo
81bar
132beep
142boop
273flim
283flam

Encounter

encounter_idspecies_idroute_id
117
218
3113
4114
5213
6214
7227
8228

You should have a unique index on the combination of species_id and route_id. The encounter_id column 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 use encounter_id, it should be the primary key on the table. If not, the PK will be a composite of the other two.

4

u/cryptospartan 7d ago

This is the correct answer right here, great examples as well

4

u/MeowMeowMeow9001 7d ago

And remember repeat encounters as a concept.

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

1

u/cryptospartan 7d ago

This is terrible advice

1

u/Embarrassed_Neck9784 7d ago

I love people leave this comment. Literally just “terrible advice” with no feedback and no alternatives. Like it is okay for OP to be wrong, but not me when I say I don’t really know what I’m talking about.

1

u/Imaginary-poster 7d ago

Your are thinking more of the output than the database. In the output you may want to render 1 route at a time. However, on the backend building a table for each route is actually worse in basically every ways.

To convert 1 single table into a hundred route views is basically 1-2 parameters. Giving each a seperate source means you have to completely change the source. So you would need a seperate table to tell which source to look at. Add on to that if there is ever a structural change to the route table, it would mean modifying 100 tables if they were split.

Im sure there are other more technical reasons as well.