Proposal: Relations Between Games

[Tracy Poff]

We want to record certain kinds of relations between games, like being in the same series. It's not ideal to just throw them all in a group with no ordering: we'd rather track that GameB is a sequel to GameA. My proposal is mostly for how to implement this technically, but I think it will be of interest to everyone.

I propose to create a new data item (a data list, I suppose, in our terminology): GameGameRelationType. The entities of this type will be the kinds of relationships that games can have to one another, like being a sequel, or being a clone. These relations will be directional: if we read one 'forward' it means (for example) "GameB is a sequel to GameA", while the 'backward' version of that same relationship is "GameA is a prequel to GameB". Or perhaps better terminology would be 'successor'/'predecessor', to be technically accurate.

A few proposed relations, as examples:

Code: Select all

IS_A_SEQUEL
Forward:  GameA is a sequel to GameB
Backward: GameB is a prequel to GameA

HAS_CHARACTERS_FROM
Forward:  GameA has characters from GameB
Backward: GameB's characters are in GameA

IS_A_CLONE_OF
Forward:  GameA is a clone of GameB
Backward: GameB is cloned by GameA

To implement this, we create a junction table in the database called (for example) GameGameRelations. It could have layout like this:

Code: Select all

GameA                 RelationType          GameB
Super Mario Bros. 2   IS_A_SEQUEL_TO        Super Mario Bros.
Super Mario Bros. 3   IS_A_SEQUEL_TO        Super Mario Bros. 2
Yoshi's Island        IS_A_SEQUEL_TO        Super Mario World
Mario Party           HAS_CHARACTERS_FROM   Super Mario Bros.
Mario Party           HAS_CHARACTERS_FROM   Donkey Kong
Mario Party 2         IS_A_SEQUEL_TO        Mario Party

Then, we simply add a column to the Game table which links to rows of the GameGameRelations table. A game will link to every relation it's a part of. This prevents us from needing to add as many new columns to the Game table as there are types of relations between games, and would let us readily do fun things like get a list of every game that is a sequel to another game, or whatever.

This kind of format could very easily be used for other relation types, like relations between people ("is the son/daughter of") or companies ("is a division of").

This proposal is primarily about the technical aspect of this, so my example relations may need tweaking, and of course many more might be added (spinoffs, enhanced remakes, parodies, companion games [like Pokemon Red and Blue], etc.). I certainly don't intend this to be the complete specification for how such a feature might be used. I'm most interested in feedback on the concept.

[MZ per X]

I'm most interested in feedback on the concept.

The concept is fine, I used a similar concept for connecting add-ons to their main game, please see Connection 4 in the Wiki. There's one thing I don't understand, though:

Then, we simply add a column to the Game table which links to rows of the GameGameRelations table. A game will link to every relation it's a part of. This prevents us from needing to add as many new columns to the Game table as there are types of relations between games, and would let us readily do fun things like get a list of every game that is a sequel to another game, or whatever.

This. Why do you need to add stuff to the game table, when all information needed to query for game relationships is already in GameGameRelations (IDs of both games, and relationship type)? What am I missing here?

Another thing that puzzles me is whether we should create this relationship at game level, or at release group level, which I did for my add-on connection. Since our RGs of one game can be quite different, it may happen that one RG can be considered a sequel to another game while another RG can't (example in this thread).

So either we decide to put such relationships at RG level, or we need to tighten our different game criteria. I think that both ways can be handled.

1) RG level relationships will allow us to hold quite different releases of games together, and will give us a game connection freedom never seen before, but also a complexity never seen before.

2) Tightening our different game criteria will simplify everything, from genre classification via game tagging to game relations, and we could hold together game releases nonetheless, if need be, using a different facility like the aforementioned game relationships.

What do you think?

[Tracy Poff]

The concept is fine, I used a similar concept for connecting add-ons to their main game, please see Connection 4 in the Wiki.

Similar in some ways, but the most substantial difference is that in my proposal the GameGameRelations table is purely a junction table--its only columns hold foreign keys for other database entities. This is convenient for several reasons, among them that we can add new relation types without a schema migration.

This. Why do you need to add stuff to the game table, when all information needed to query for game relationships is already in GameGameRelations (IDs of both games, and relationship type)? What am I missing here?

Primarily as a convenience. Technically we never need to make a relationship bidirectional, but it's common practice to do so. If we do this, then a Game object will have a convenient Game.relations property that gets sent out when a client requests it. I supposed that sort of information was likely to be desirable more often than not.

Another thing that puzzles me is whether we should create this relationship at game level, or at release group level, which I did for my add-on connection. Since our RGs of one game can be quite different, it may happen that one RG can be considered a sequel to another game while another RG can't (example in this thread).

I'm not sure which game you mean as an example, there. Doki Doki Panic/Mario 2, maybe? Anyway, yes, I also thought about where relations would need to go, and I thought that the Game level was correct.

Personally, I'm in favor of having a larger number of 'different games'. If a release is so different that you could consider another game a sequel to it but not another release, I'd say that's probably a good indicator that it should be a different game. Also, if we make use of these kinds of relationships and split games more often, then I think it makes it easier on us. Example:

Code: Select all

GameA              Relation               GameB
Pokemon Ruby       IS_A_COMPANION         Pokemon Sapphire
Pokemon Emerald    IS_ENHANCED_REMAKE     Pokemon Ruby
Pokemon Emerald    IS_ENHANCED_REMAKE     Pokemon Sapphire
Super Mario USA    IS_MODIFIED_VERSION    Doki Doki Panic
Super Mario USA    IS_A_SEQUEL            Super Mario Bros.
Super Mario 2      IS_A_SEQUEL            Super Mario Bros. [But Doki Doki Panic isn't]
Super Mario 3      IS_A_SEQUEL            Super Mario 2     [But not a sequel to Super Mario USA]

As long as we can use relations to record the close connections between games, we don't really lose anything by splitting them more often.

1) RG level relationships will allow us to hold quite different releases of games together, and will give us a game connection freedom never seen before, but also a complexity never seen before.

This complexity is the main reason I didn't want to do things at RG level. Either we only end up connecting some of the RGs (say, the earliest ones), in which case conceptually we clearly are wanting to connect the games (since we don't feel the need to connect each release group), or else we end up connecting all of them to each other, in which case n release groups on a prequel and k release groups on the sequel will require n^k connections--unmanageable!

2) Tightening our different game criteria will simplify everything, from genre classification via game tagging to game relations, and we could hold together game releases nonetheless, if need be, using a different facility like the aforementioned game relationships.

So, as I said, this would be my preference. I think that if we have an edge case where we don't know which to do, splitting the game won't do us any harm. On the contrary, since we can then use the Game-to-Game relationships to describe just how the problematic RG is different from the others, we'll gain the ability to better document things.

[Rola]

I'm not sure about "HAS_CHARACTERS_FROM". If this is another connection to build game series, I'm against.

Prequel/sequel connections are used by IMDb, because they don't have "film series" view. I hope you'll retain the series view like MobyGames did (only improve it). Say, there are sometimes series spin-offs that don't fit anywhere.

If you're really after game characters - some game databases (we could say IMDb as well) track characters like they track the cast of actors. For me such in-depth knowledge is a bit extra for a database like Oregami (it fits a dedicated wiki better).

[gene]

Shouldn't we model things like "games series" and "franchise" as an own entity and create relations between game entities and the games series entity?
This way we could add additional information to the series and the franchise objects.

And the "game character" thing:
What about the idea of creating own entities even for game characters? And then - you may guess it - we could draw relations between games. And even between characters!

[hydr0x]

Primarily as a convenience. Technically we never need to make a relationship bidirectional, but it's common practice to do so. If we do this, then a Game object will have a convenient Game.relations property that gets sent out when a client requests it. I supposed that sort of information was likely to be desirable more often than not.

Not really. It's a pointless hack used by developers without a clue acting as database designers, trained mainly on O/R-mapping tools. One should never duplicate information in a database like that unless there is a very very good reason to. There is absolutely no benefit to it here and it clearly violates fifth normal form. Of course, this is the typical convenience/speed vs. correctness/robustness debate that's behind the whole NoSQL movement. But, this database doesn't even come close to the kind of size (and especially the number of queries) that makes sacrificing consistency and correctness necessary. Redundancies should be avoided completely, a database of this scale has no reasons to introduce them.

By the way, there is no such thing as unidirectional or bidirectional when it comes to relational databases, unless you work with some kind of O/R-Mapper on top, in which case your objects relations will have a direction.

[Tracy Poff]

I'm not sure about "HAS_CHARACTERS_FROM". If this is another connection to build game series, I'm against.

All of the actual relations are just examples I came up with quickly to show that the mechanism would be versatile. However, since you brought it up...

I don't see 'has characters from' as being an alternative to other treatment of game series. Consider:

• Super Smash Bros. has characters from Super Mario Bros.
• Super Mario Kart has characters from Super Mario Bros.
• Mario Party has characters from Super Mario Bros.
• Mario Tennis has characters from Super Mario Bros.
• Super Meat Boy has characters from Bit.Trip Beat
• Super Meat Boy has characters from Alien Hominid
• Super Meat Boy has characters from Castle Crashers
• ...

While you could say that the Nintendo examples involve games that could be considered vaguely part of a series, the Super Meat Boy examples clearly are not. The only relation the various games in the Super Meat Boy examples have is that they share characters--some version of this relation must exist, or we simply couldn't track that kind of info in the database.

Prequel/sequel connections are used by IMDb, because they don't have "film series" view. I hope you'll retain the series view like MobyGames did (only improve it). Say, there are sometimes series spin-offs that don't fit anywhere.

It would be entirely possible to display a whole series (i.e. everything connected by prequel/sequel relations) on a single page, of course.

I'd argue that the prequel/sequel method is better than just having a series collection: consider that Super Mario USA and Super Mario 2 (The Lost Levels) are both sequels to Super Mario Bros., but Mario 3 is only really a sequel to Mario 2. And, of course, there are plenty of series with long branches of multiple sequels.

Spinoff series are a little more complicated. I have the germ of an idea for how to handle this, but I'll have to think about it a little more. Suffice to say that I don't see this mechanism as being the right tool for that job.

In short: being 'part of a series' (particularly a big series, like 'Mario franchise') tells you something about one game, by itself. Super Princess Peach is 'part of the Mario franchise'. The purpose of this proposal is to deal with relationships between games. Super Mario Galaxy 2 is related to Super Mario Galaxy in a certain way--being a sequel.

If you're really after game characters - some game databases (we could say IMDb as well) track characters like they track the cast of actors. For me such in-depth knowledge is a bit extra for a database like Oregami (it fits a dedicated wiki better).

And the "game character" thing:
What about the idea of creating own entities even for game characters? And then - you may guess it - we could draw relations between games. And even between characters!

I do actually think it would be a good idea for us to have a character database as well, but that's something that can wait until we have the basic database set up. For now, I think something like a 'has characters from' relation will be a good-enough stopgap measure, which is useful in the short term and which will help us out if we later add a character database.

Shouldn't we model things like "games series" and "franchise" as an own entity and create relations between game entities and the games series entity?
This way we could add additional information to the series and the franchise objects.

As I mentioned above, I do have an idea for how to handle this, but I see the two as different problems, best solved with different tools. The 'relationship' between Super Princess Peach and Mario Teaches Typing is very different from the relationship between Super Mario Galaxy and Super Mario Galaxy 2.

I'll try to write up my idea for this soon--look for it in a separate thread.

[Ultyzarus]

As far as sequels are concerned in the Mario series case, most of the games aren't actual sequels, except maybe Mario Galaxy 2 as a sequel to Mario Galaxy, and Mario 2 (the lost levels) a sequel to Mario bros. The others are just numbered games set in the same world/universe that don't follow a specific timeline and are therefore not sequels

I'm all for entitized game series by the way.

[Tracy Poff]

Not really. It's a pointless hack used by developers without a clue acting as database designers, trained mainly on O/R-mapping tools. One should never duplicate information in a database like that unless there is a very very good reason to. There is absolutely no benefit to it here and it clearly violates fifth normal form. Of course, this is the typical convenience/speed vs. correctness/robustness debate that's behind the whole NoSQL movement. But, this database doesn't even come close to the kind of size (and especially the number of queries) that makes sacrificing consistency and correctness necessary. Redundancies should be avoided completely, a database of this scale has no reasons to introduce them.

By the way, there is no such thing as unidirectional or bidirectional when it comes to relational databases, unless you work with some kind of O/R-Mapper on top, in which case your objects relations will have a direction.

Okay... I don't claim to be an expert on database design. If you are, I invite you to lend your voice to these discussions and help us to do things the right way. I'll be very happy to be able to rely on someone with more experience, when making design decisions. My lack of experience is something that causes me quite a bit of worry, in truth.

That said: we are, in fact, using an ORM tool, Hibernate. Sorry if that makes me a bad person.

As for sacrificing consistency: if the extra column is managed by the ORM, there should be no inconsistency, unless there's a bug in the ORM. I don't intend to allow manual updates.

One way or another, we want to be providing this relation data when someone requests a Game record. If we don't have a 'relations' property on each Game, then we'll instead have to provide a function which does a query to find all the relations a game is involved in, every time the game's record is pulled (modulo caching), and then sends it along. Not a big deal, maybe--I'm not sure about the relative performance hit for a query like that vs. an extra column. I guess if we have an index on each column, it would be cheap enough.

So: if this is the wrong way, what do you propose would be the right way?

[jotaroraido]

What about games based on outside (non-game) properties? Say, X-Men for the NES, X-Men: Children of the Atom on the Saturn, and X-Men Destiny on the Xbox 360. They should obviously be linked together in some way, as they're all based on the same franchise, but you'd be hard pressed to say any one is a "sequel" to another, or "uses characters from" another specific game.

[Rola]

One word of explanation:
prequel =/= predecessor

Prequels are made after the first work (just like sequels), however their action is set before the first work.


* * *

Imagine a game with Sherlock Holmes and Dr. Watson. Does every single other game with those characters deserve "HAS_CHARACTERS_FROM"? Of course not.

Now, if we handle it like IMDb does - character as tracked entity - linking "Sherlock Holmes" to every single game with Sherlock Holmes makes sense.

How are characters different from my proposal of advanced tags?

[Tracy Poff]

One word of explanation:
prequel =/= predecessor

Prequels are made after the first work (just like sequels), however their action is set before the first work.

Yes, I know this, and I agree that in particular the word 'prequel' as the opposite of 'sequel' doesn't work. I guess you could say "GameA has a sequel GameB", or something, but I wasn't interested in coming up with perfect descriptions, just then. That's why I said in my original post that the proper term would probably be 'predecessor'/'successor' instead, but I didn't bother to correct the rest of my example, since I was just using them as examples of the technical mechanism I was proposing. Sorry if this error was too distracting.

Imagine a game with Sherlock Holmes and Dr. Watson. Does every single other game with those characters deserve "HAS_CHARACTERS_FROM"? Of course not.

Now, if we handle it like IMDb does - character as tracked entity - linking "Sherlock Holmes" to every single game with Sherlock Holmes makes sense.

Of course we wouldn't do something like that with Holmes or whatever. Like everything else, we'd have to use good judgment, and for 'public domain' characters we'd probably want to use something like a group or tag, yes.

How are characters different from my proposal of advanced tags?

I assume you're talking about this? Characters are very different from tags, because, much like games, they have properties of their own. If we model characters in the database, we could track properties like sex, age, hair color, species, relationships between characters (e.g. CharA is the sister of CharB), and so forth. When we attach them to games, we could give them roles, like 'CharA is a protagonist in GameA', 'CharB is a sidekick in GameA', 'CharC is an antagonist in GameA', 'CharA is a controllable character in GameA', 'CharC is an NPC in GameA', etc.

By treating characters as first-class database entities, we could then execute useful searches on them. We could produce a list of every game with a male protagonist and a blonde female antagonist, for instance. Or every game with a protagonist whose father appears in the same game. There are many possibilities.

But, as I think this example makes clear, actually implementing this will take quite some effort, and I think we'd be better served, for the moment, working on getting a good basic specification for the database, with a server up and running, before we go too far with extra features. If we build the software and the construct database correctly, it should be very possible to extend it in the future, without too much pain.

[gene]

Wohoo, this discussion get from zero to hundred percent immediately, that's great!

Nevertheless, I want to suggest that we go one step back:
While it makes fun and is appealing for developers to come up with a technical solution simultaneous with the feature specification, it may lead to the wrong direction.
We should at first try to specify what we want to achieve, and afterwards we should talk about the implementation. That helps to concentrate on the features.

What are the detailed features concerning game series, franchises/universes (e.g. "Mario universe") and game characters?
I would appreciate that we stop discussing in this thread and open new threads for the detailed feature discussions.

BTW, I am also a friend of avoiding redundancy. As hydr0x says: we are fare away from the state where we can say "redundancy is ok to reach certain non functional requirements" (e.g. performance). We can later decide to save some things redundantly, but not now.

[Tracy Poff]

Wohoo, this discussion get from zero to hundred percent immediately, that's great!

Nevertheless, I want to suggest that we go one step back:

Yeah, this discussion got very messy very fast. This is probably a sign that I tried to design an implementation too soon before the requirements were set, so I apologize for that. For reference, the problem I was trying to solve was this:

But I think that game series shouldn't be handled using the game group implementation. Instead, we should implement something similar like UVL does with game relationships, as in two games being linked to each other with a relationship type like "prequel to", "sequel to", "port of", or similar.

So... I did actually have a particular problem I was trying to solve, though I may have been a bit too enthusiastic to show off how general my solution was.

BTW, I am also a friend of avoiding redundancy. As hydr0x says: we are fare away from the state where we can say "redundancy is ok to reach certain non functional requirements" (e.g. performance). We can later decide to save some things redundantly, but not now.

I'm still not quite sure that my suggestion was redundant in a bad way. As I had mentioned before, my previous database experience involved SQLAlchemy, and it was certainly encouraged to include a backref on objects when you would need to access a relation from the 'other side.' In fact, now that I look, Hibernate's own Best Practices suggest using bidirectional associations in most cases. Though I am of course prepared to let go of this proposal, I really would appreciate if you could enlighten me as to what's wrong with including a backref from a Game object to a GameGameRelations row.

[hydr0x]

I'm still not quite sure that my suggestion was redundant in a bad way

I'd argue that redundancies are always bad and should only be introduced if necessary(!). O/R-Mappers have to have a concept of direction as objects do have them. They are forced to map this to the database. But, as far as I can tell from the SQLAlchemy documentation, adding backref or not to a m:n-Relation does not change anything on the database end. It certainly doesn't seem to add the redundant column in one of the data tables, but rather just creates a relation table. All backref does is add collection objects to both data classes.

Anyways, it's pretty pointless to discuss this. A) the guys aren't yet in the implementation phase B) hibernate will force it's own idea of database design down your throat

As for the Hibernate best practice, it's questioned by quite a few devs, see this discussion for an example.