Databases & I: an Adventure

Drawing up database connections aren’t as difficult as you might think. Say for example, you are creating a choose your own adventure web application, where the user would be able to see their choices and be able to reset their progress if they hit a bad end.

What would you need in your database? You know for sure that you will need a User table and a Story table, but what else?

That all depends on what you want to present to the user.

---

User Table

Minimum datapoints needed:

1. Id (auto generated)
2. Username
3. Password

Absolutely needed to keep track of input per user, aka where the user left off in the story and the choices they've made in the story.

---

Story Table

Minimum datapoints needed:

1. Id (auto generated)
2. Title
3. Description

Think of this like the front and back cover of a book where it says the title and the brief blurb about the story. If we wanted this to be a regular story without the choose your own adventure aspect, we could put all of the story in here but...

---

Events Table

Minimum datapoints needed:

1. Id (auto generated)
2. Story_id (which story does the event belong to?)
3. Event description (what you are displaying to the user)

The story table could hold all of the events but if you ever want to expand to more stories, you would end up getting all of the events mixed up between stories.

Not to mention that in order to be able to have the user pick what they want to happen, you need to have events (chapters) separated so that when they choose an option it can change the story trajectory.

---

Choices Table

Minimum datapoints needed:

1. Id (auto generated)
2. Choice description (what you are displaying to the user)
3. Event_id (what event it belongs to)
4. Next Event Id (what event goes next if selected)

If we had the choices in the events table it would be extremely difficult to map out which choice leads to what event. Separating them out makes it easier to navigate plus then you can get a little creative on what you want each choice to include: a little picture to symbolize the choice, a sound effect to be played when it's selected?

---

Ok so those are pretty basic, but how are we going to be able to track the user choices? Making the User have an array data point is not the easiest thing to do but what is the alternative? More tables!

---

User Stories Table

Minimum datapoints needed:

1. Id (auto generated)
2. User_id
3. Story_id

This table will be our connection (join table) between the User and the Stories. Having this table will make it so that if we ever add more stories, our database will be easily expandable and the user can do more than one story at a time.

---

User Choices Table

Minimum datapoints needed:

1. Id (auto generated)
2. Userstory_id
3. Event_id
4. Choice_id

Finally down to the meat and potatoes of what we actually wanted in the first place. This table will be our connection between Users and their choices.

However, we don't want to just have a User data point and a Choice data point.

For one thing, the choice belongs to an Event. Without that context, it could get confusing for the user to determine what they did in the story at whatever time. So we definitely need to add an Event connection.

Secondly, if we just link the user choice directly to the user, it becomes incredibly difficult to not only delete the user choices when it comes time to reset the story but it also makes it difficult for the User to determine what Story the choices are from as you would have to go several layers deep to get that connection back (Choice > Event > Story).

Connecting it to the User Stories table instead of the User table will allow us to not only easily reset the User's progress (using dependent destroy on the User Stories table) but it will also allow us to easily separate out the choices per Story on the front end.

---

All of these tables allow the project to expand with time; whether that be with more users, stories, or even expanded mechanics (inventory system maybe?). I'm definitely excited to see where this could go. The diagram above shows a little more than what I previously described, but that's the beauty of this project, there is so many ways to expand!

If you found this interesting, checkout the project on Github

( Header image from here )