Your idea seems to be gaining a lot of support.
I did a brief checking on the system changes that would be necessary for this to be possible. Here is an unordered list of some things I spotted.
― In each branch, would need to be recorded also extra information about its significance: whether it should be featured by default in long listings, or to hide under some link.
― Possibly, UI-wise, implement different versions of same game's movie with tabs. But alas, tabs cannot display multiple items at once, so only one of them would be showcased at given time.
―― Hiding some movies behind tabs would mean that user's attempt to use browser's "search" function would fail to find movies that happen to be hidden under tabs. Also, it makes the tabular formatting of the movies page a lot more jigglywiggly, because each movie would now be in its own table (tabs cannot split a table). Inflation in the amount of HTML would also be experienced, leading to longer transmission times and longer rendering times (the 600 kB Movies-NES page already takes about 20 seconds to fully render).
― To track hacks based on a game, some relationship needs to be written in data between different games. Would it be movie<->movie? Would it be an some_kind_of_anchor<->multiple_movies type relationship? It presents a database design dilemma as well as a maintenance UI dilemma.
― How about variations of hacks? Would they be listed? Or just the most prominent movie of a hack? How would that be decided?
― Would there also be a link to documentation pages? How would that be indicated in the database?
― Possibly, needs a tool to maintain relationships between different movies, their branches and their relative significances, and the links to different articles.
―― Also publishers need to use the tool and verify at each movie they publish, whether it belongs to an existing movie group or creates a new one.
The movie table in the database is currently declared as follows (key constraints and indexes omitted for brevity):
CREATE TABLE movie
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
playerid SMALLINT NOT NULL,
systemid TINYINT NOT NULL,
gamename VARCHAR(255) NOT NULL,
nickname VARCHAR(255) NOT NULL, # Such as "100%"
gameversion VARCHAR(255) NOT NULL DEFAULT 'USA',
romname VARCHAR(255) NOT NULL DEFAULT '',
lastchange INT(11) NOT NULL DEFAULT 0,
# Movie message is now found in site_text only.
# Which submission was this movie derived from.
submissionid INT NOT NULL DEFAULT -1,
obsoleted_by INT NOT NULL DEFAULT -1, # which movieid obsoletes this one
# Webmaster's pick?
recommended ENUM('N','Y','T')NOT NULL DEFAULT 'N',
# Hacked game or otherwise inpure movie
inpure ENUM('N','Y')NOT NULL DEFAULT 'N'
);
I considered adding a "nickpriority" field, which would indicate which movies to showcase when an "all NES movies" list is conjured up and there are four Metroid movies there. It would also indicate in which order to put the tabs. But it won't solve most of the problems listed above.
If someone here is an expert on designing this kind of stuff ― also from the database point of view, help would be appreciated.
Here are some of my thoughts on the matter. I'm not sure any of them would be useful, but it's still better to have some input, I guess?
Bisqwit wrote:
― In each branch, would need to be recorded also extra information about its significance: whether it should be featured by default in long listings, or to hide under some link.
Well, for example, in SDA's game lists, they give the priority to the shortest game completion (from beginning to end), which is reasonable. First of all, we'd have to decide how many prioritized movies should we feature this way — only the "any%'s" (fastest non-broken completion from beginning to end; with "broken" referring to runs like glitched Zeldas and all that stuff), or, say, both any%s and 100%s (="full game completion"), or something like any% and highest rated other movie for this game? It seems unwise to have more than two prioritized categories in any case, though. For games geared towards entertainment (autoscrollers, 1-on-1 fighting games), movie(s) with the highest entertainment rating should be prioritized. If we have such things as movie categories and ratings in the database, we should try to put them to good use whenever possible.
Bisqwit wrote:
― Possibly, UI-wise, implement different versions of same game's movie with tabs. But alas, tabs cannot display multiple items at once, so only one of them would be showcased at given time.
―― Hiding some movies behind tabs would mean that user's attempt to use browser's "search" function would fail to find movies that happen to be hidden under tabs. Also, it makes the tabular formatting of the movies page a lot more jigglywiggly, because each movie would now be in its own table (tabs cannot split a table). Inflation in the amount of HTML would also be experienced, leading to longer transmission times and longer rendering times (the 600 kB Movies-NES page already takes about 20 seconds to fully render).
Tabs are good, but, as you say, they can put noticeable burden on the page layout and hinder in-page searching. However, even though they most likely won't be usable to contain movie items in them, they can serve a similar purpose to ease navigation (like here, for example). It needs more thinking, but I'm sure a working solution will arise soon.
After all, it's not unreasonable to create separate pages for each game to break/redirect the HTML weigh from one page to several.
Bisqwit wrote:
― To track hacks based on a game, some relationship needs to be written in data between different games. Would it be movie<->movie? Would it be an some_kind_of_anchor<->multiple_movies type relationship? It presents a database design dilemma as well as a maintenance UI dilemma.
In my opinion, hacks should only relate to the game they've been spawned off as a subgroup of movies. I guess it's "some_kind_of_anchor<->multiple_movies" in your example, with anchor being the game's name or similar identifier — for games that are named differently for different regions.
Bisqwit wrote:
― How about variations of hacks? Would they be listed? Or just the most prominent movie of a hack? How would that be decided?
What do you mean by variation of hacks? If it's regarding the existance of multiple hacks for the same game, then each hack with movies published for it should be a subgroup related to that game and not other hacks. The rules of listing will most likely be same same as with the regular movies. In case each game gets its own page, I think it would be fine to list the movies of its hacks only on that page and nowhere else. I'm not entirely sure how to treat them in case of centralized movie listings, but I think it's what we're currently trying to avoid using, with this new layout paradigm.
Bisqwit wrote:
― Would there also be a link to documentation pages? How would that be indicated in the database?
This, too, needs either a specialized tab with links or a separate game-related page.
Bisqwit wrote:
―― Also publishers need to use the tool and verify at each movie they publish, whether it belongs to an existing movie group or creates a new one.
I assume the tool will be located on server's side and be interacted with by some kind of web interface? (Ajax'ed, as in case with wiki editing?)
Warp wrote:
Edit: I think I understand now: It's my avatar, isn't it? It makes me look angry.
the way I imagined it was this:
Definitions and Design
We need a new table for games. My definition of a game would be:
Every version of a game within one system is the same game. Rockman 2 (J) and MegaMan 2 (U) are the same game for the purpose of this database. Ocarina of Time (V1.0) and (V1.1) are the same game. PAL- and NTSC-Versions are the same game. If they will be treated differently due to major changes, that'll be visible in the obsoletion graph and doesn't need any distinction in the game-table.
One exception: SMB2 (J) and SMB2 (U) are different games, because they're obviously different games (duh!). SMB2 (U) and DDP are different games as well.
Because of the existing grouping by consoles SMB (NES) and SMB AllStars (SNES) can not be the same game - but we usually don't have runs of ports on a different system anyway.
You could sum it up this way: if (by current rules) a movie on one rom could obsolete a movie on a different rom, they're both the same game.
Obsoletion:
A movie is only allowed to obsolete another movie if it was played on comparable (or strictly superior) settings. Bionic Commando and HNF won't obsolete each other, we'll have both the fastest BC and the fastest HNF-movie published.
Don't worry, we will still have fights, since we need to decide which one will be featured in the movie-listing and which one will be hidden on the game-specific page ;)
with 'strictly superior' settings I mean: a full-game-run can still obsolete the 1st-track-only run of F-Zero. A 2-player-run can still obsolete a 1-player-run if there's no reason to keep the 1-player-run around.
Concept demos: there is no such thing. It'll only lead to arguments about it's definition anyway. The Concept Demo/Other-section currently contains three kind of movies:
- movies on hacks (will find their place)
- movies with goals other than plain game completion (can be filed along with the regular movies, since they've been tagged by movies.nickname already)
- dual- and quad-runs. We could create a new game ("MegaMan 3, 4, 5 & 6") for each, so they'll fit in nicely. We just won't be able to feature quad-runs of games on different consoles this way, but incompatible controller-layouts should prevent that anyway.
Data Structure
for example:
Table Games
id systemid name original resources
1 <snes> Super Metroid NULL SuperMetroidTricks
2 <snes> Metroid Redesign 1 MetroidRedesignTricks
3 <nes> Rockman/Megaman 2 NULL MegaManTricks
4 <nes> Super Mario Bros 2 J NULL ''
5 <nes> Super Mario Bros 2 U NULL ''
Add a "description"-field as well, and drop a bunch of text with markup (wiki or html) in there. Might be useful, for example to link to SMB AllStars from the SMB-Page and vice versa.
If there is more than once resource-page for a certain game it's probably best to list only one resource-page, and add links to the other pages there. Implementing a 1-to-N-relation here would be overkill (IMHO).
movie-table:
- add boolean "prominent" (i.e. appears in movie listings)
- add gameid column
- systemid and inpure are obsolete (gameid -> systemid / gameid -> original != NULL). The clean way would be to remove the fields and adjust all scripts that use them. The easy way is to leave them in and have them automatically maintained.
The movie-table will still contain the "rom name"-field, thus removing any need for distinction between different hack- or game-versions (other than not to obsolete non-comparable versions of course).
Initial Data Entry
- the game-table has to be created and each movie has to be assigned a gameid. I've been working on a script to get as much information as possible from the existing data, but it's not complete. It'll still need manual editing which I haven't even started.
- the logic which currently decides what to list on the movie listing has to be used to fill initial values of the "prominent" table.
- the obsoletion graph needs some adjustments. We've had moments where a movie obsoleted another movie in a different category due to the "but we can have only 2 non-obsoleted movies"-restriction, those obsoletions can be removed. That's not urgent though and can be done over time when errors are noticed.
Data Maintenance
when a movie is published, make a good guess about the game:
- if this movie is obsoleting another movie, use the previous gameid
- if there's already a movie in the database with the same rom-name, suggest it's game.
- otherwise allow the publisher to select an existing movie or to create a new one
I haven't seen the publication interface, so I can't do much but guess about proper implementation.
UI LayerNew filter: all movies of a specific game. Displays:
Gamename and description (from the games-table)
all published non-obsoleted movies of this game, including links to list the obsoleted movies as well (maybe add something like the "History of this entry"-Tab below each movie?)
Show links to the game-specific movies of each hack. If this is a hack, show a link back to the original game
Show a link back to all system's movies (i.e. 'Movies-SNES.html')
Show a link to the resource page
edit: quick and ugly mockup: http://www.authmann.de/misc/Game_SuperMetroid.png - not supposed to show the design, only the general layout and information structure
Existing movie lists
I still prefer the suggestion as I made it on that mockup you quoted. No tabs, no drastic changes, just a bit of grouping and a couple of new links. Of course it'll now show movies flagged as "prominent" and not just every non-obsoleted movie.
That won't increase the html-size too much. It will add a couple more database-querys, but that page is static enough to be cached for longer periods (except torrent statistics?)
As a result, the lists will contain only non-hacked games, but each game has a link to it's hacked versions. We could replace the the "Concept Demo/Other"-section with "Hacked games", linking only to games where original != NULL.
movie detail view (i.e. 123M.html)
- needs a link back to the system- and game-pages. The "More movies"-tab looks fitting.
- since there's only one movie visible, there should be enough room to display the game's description and resources too.
uh.. yeah.. what did I miss? Any obvious flaws in the design, or any disagreement about my definitions?
edit: mockup added (link above)
Definitions and Design: waiting for feedback
Data Structure: waiting for feedback
Initial Data Entry: after ditching my previous attempt and writing the script from scratch, the data turned out to be quite nice and needs much less manual work than I expected. The automatic part is almost complete, I do have a working game-list. Still needs manual editing (renaming a few, linking hacks, adding description/resources)
Data Maintenance: those scripts are not within my reach, but coding the tools is quite straightforward
UI Layer: this is where most of the work will be. This and adjusting the old scripts to the new data structure where needed.
some more feedback needed: I have replaced some inconsistent spellings. The spelling I considered "better" is seen on the right. If any of you disagree on any of those choices, tell me.
'Legend of Zelda, The' => 'Legend of Zelda',
'Mc Kids' => 'M.C. Kids',
'Nightmare On Elm Street' => 'Nightmare on Elm Street',
'Super Mario Bros. 2' => 'Super Mario Bros 2',
'Super Demo World' => 'Super Demo World - TLC',
'The Legend of Zelda - A Link to the Past' => 'Zelda: A Link to the Past',
'Contra - The Hard Corps' => 'Contra - Hard Corps',
'Sonic 3 and Knuckles' => 'Sonic 3 & Knuckles',
'Castlevania: Circle Of The Moon' => 'Castlevania: Circle of the Moon',
also, what about Pokemon Red / Blue? A run on one of them obsoleted a run on the other. Should they be treated as one game, or as two seperate games? I have no idea how much they differ..
Unless there's a problem at an earlier point, it's now up to bisqwit to determine if the proposed changes are technically feasible. It all depends on the amount of changes required in the existing scripts..
'Legend of Zelda, The' => 'Legend of Zelda',
'Super Mario Bros. 2' => 'Super Mario Bros 2',
'The Legend of Zelda - A Link to the Past' => 'Zelda: A Link to the Past'
Why would you want to remove the article from The Legend of Zelda? It's ungrammatical to remove it and it belongs in the name. Same goes for SMB2. Just check the cartridge, the full stop is there (and for a reason). The last one looks horrible. "The Legend of" is a part of the name and needs to be there. Maybe the article should be moved, though, like with the first one. Legend of Zelda, The - A Link to the Past or Legend of Zelda - A Link to the Past, The. I'm not sure which one is correct, but IMO either is better than the one you suggest.
In response to Tub's comments.
> We need a new table for games.
Doable, though it really calls for a tool to maintain those "which movies belong to this game's group" relationships and for creating new game entries etc then, and modification of the publication tool to contain also a part of that tool like it already contains a part of the player list management tool.
> PAL- and NTSC-Versions are the same game.
> If they will be treated differently due to major changes, that'll be visible in the obsoletion graph and doesn't need any distinction in the game-table.
Perhaps so.
> Obsoletion:
> A movie is only allowed to obsolete another movie if it was played on comparable (or strictly superior) settings.
Sounds good.
> with 'strictly superior' settings I mean: a full-game-run can still obsolete the 1st-track-only run of F-Zero. A 2-player-run can still obsolete a 1-player-run if there's no reason to keep the 1-player-run around.
Sounds good.
> Concept demos: there is no such thing.
Good thing.
> Data Structure
> for example:
Something like that perhaps, yes.. I would label "original" as "derived_from" though, and the "resources" entry might be insufficient, though.
> Add a "description"-field as well, and drop a bunch of text with markup (wiki or html) in there. Might be useful, for example to link to SMB AllStars from the SMB-Page and vice versa.
Hmm.
Cannot the resources page link be embedded in the description instead?
> movie-table:
> - add boolean "prominent" (i.e. appears in movie listings)
> - add gameid column
> - systemid and inpure are obsolete (gameid -> systemid / gameid -> original != NULL).
Agreed.
> The movie-table will still contain the "rom name"-field, thus removing any need for distinction between different hack- or game-versions (other than not to obsolete non-comparable versions of course).
The "rom name" field is really a quite hidden feature on the site. Most people don't even know it exists or how to view it. I don't wish to advertise ROM sets too much.
> - the logic which currently decides what to list on the movie listing has to be used to fill initial values of the "prominent" table.
Everything is listed. But there are approximately 500 different views to the movie listing. Examples: Movies-All.html Movies-GBA.html Movies-GBC.html Movies-GB.html Movies-Genesis.html Movies-N64.html Movies-NES.html Movies-<search by name>.html Movies-SGB.html Movies-SNES.html Movies-___up.html Movies-RatingY-Rec.html> - the obsoletion graph needs some adjustments. We've had moments where a movie obsoleted another movie in a different category due to the "but we can have only 2 non-obsoleted movies"-restriction, those obsoletions can be removed. That's not urgent though and can be done over time when errors are noticed.
I'm not sure about that.
> I haven't seen the publication interface, so I can't do much but guess about proper implementation.
You have now...
> New filter: all movies of a specific game. Displays:
Some good ideas there. I'm keeping options open.
To be more helpful…
– It looks too cluttered. Way, too cluttered.
– Probably it wouldn't show the game description if the page lists more games?
– The question about variations of hacks still exists.
> Existing movie lists
> I still prefer the suggestion as I made it on that mockup you quoted. No tabs, no drastic changes, just a bit of grouping and a couple of new links. Of course it'll now show movies flagged as "prominent" and not just every non-obsoleted movie.
Well, the names of the movie pages should be changed then... Because "All NES movies" does not list all NES movies, but only some "prominent" ones.
Basically the movies structure would change. Doable.
I'm looking forward to the rewrite of the entire movies.php file…
> That won't increase the html-size too much. It will add a couple more database-querys, but that page is static enough to be cached for longer periods (except torrent statistics?)
Times are displayed according to user's timezone (from forum profile via login). Torrent statistics are relatively realtime data. Ratings are also relatively realtime data. Edit links are shown if the user has access to that, not otherwise; same for ratings. Etc. It is a caching horror. And then are the zillions of search options. (See above for mention of ~500 different views).
> movie detail view (i.e. 123M.html)
> - needs a link back to the system- and game-pages. The "More movies"-tab looks fitting.
> - since there's only one movie visible, there should be enough room to display the game's description and resources too.
"tab"? Probably not.
Tub, if you want SVN access to the site PHP code, that is doable :)
moozooh wrote:
What do you mean by variation of hacks?
Super Demo World all levels, Super Demo World minimalist, etc.
> We need a new table for games.
Doable, though it really calls for a tool to maintain those "which movies belong to this game's group" relationships and for creating new game entries etc then, and modification of the publication tool to contain also a part of that tool like it already contains a part of the player list management tool.
What do we need?
- on publication: select a game or add a new game
- changing an existing game's name, description, resource-page, ..
- maybe merging two games (if a publisher accidentally adds a new game instead of choosing the existing one)
anything else?
There is an alternative: don't create the games table, clean up the movies.name column, and use that for grouping. But that won't allow game descriptions, relationships of hack <-> original game, and grouping by a VARCHAR column isn't too fast. I really think the games-table is the way to go.
and the "resources" entry might be insufficient, though.
[...]
Hmm.
Cannot the resources page link be embedded in the description instead?
Well, if a simple link is insufficient, have the resources-column contain markup as well instead of a page-name.
Of course both columns could be joined. I suggested two columns because I planned to display the description on top of the page and the resources-page at the bottom. Depending on the filter, there might be a reason to display only one of them.
The "rom name" field is really a quite hidden feature on the site. Most people don't even know it exists or how to view it. I don't wish to advertise ROM sets too much.
good point.
anyway, forget everything I said about the romname-column and replace it with the existing gameversion-column which I totally overlooked. That column looks like it fits the job.
As there is already a clear distinction in the database between "game" and "game version", the transition doesn't change that much semantically.
Everything is listed.
so:
- initially set any non-obsoleted movie to "prominent".
- add a tool to change the prominence-status of a movie
anything missing?
> - the obsoletion graph needs some adjustments. We've had moments where a movie obsoleted another movie in a different category due to the "but we can have only 2 non-obsoleted movies"-restriction, those obsoletions can be removed. That's not urgent though and can be done over time when errors are noticed.
I'm not sure about that.
can you elaborate? About which points do you disagree, and why?
> New filter: all movies of a specific game. Displays:
– It looks too cluttered. Way, too cluttered.
that's a matter of design, which my mock-up clearly wasn't even trying to be good at. ;)
– Probably it wouldn't show the game description if the page lists more games?
yes. Game-Description, History of this Entry and links to hacks and resources should only be displayed when filtering by game or displaying only one movie.
Any filters possibly displaying movies of multiple games will only contain links to "show all movies of this game".
If possible, the movies should be grouped by game as in my first mockup (Movies-<System>), otherwise (Movies-Rating etc) not.
– The question about variations of hacks still exists.
see above, gameversion column.
Well, the names of the movie pages should be changed then... Because "All NES movies" does not list all NES movies, but only some "prominent" ones.
As it does now. It's just that the movies not listed are called "obsoleted" now, and "either obsoleted or not prominent" after the change.
Actually it will still show all non-obsoleted movies, it's just that some are shown with screenshots and explanations, and some will be nothing more than a link. Semantically details.
I'm looking forward to the rewrite of the entire movies.php file…
hu, rewrite? There's changing the SQL-query at the beginning (adding the JOIN, including the new game-filter) and fiddling with the template logic to add the new information when needed.
I don't see where rewriting from scratch is needed (unless I misunderstood your meaning of 'rewrite'), but since you know the code better than I do I'll trust your judgement.
Times are displayed according to user's timezone (from forum profile via login). Torrent statistics are relatively realtime data. Ratings are also relatively realtime data. Edit links are shown if the user has access to that, not otherwise; same for ratings. Etc. It is a caching horror. And then are the zillions of search options. (See above for mention of ~500 different views).
good point. caching at a different layer either won't work either or just won't help. Your call if the additional server load is bearable.
moozooh wrote:
What do you mean by variation of hacks?
Super Demo World all levels, Super Demo World minimalist, etc.[/quote]
movie.gameid -> "Super Demo World"
movie.gameid -> derived_from -> "Super Mario World"
movie.gameversion = "" or "TLC"
movie.nickname = "all levels" or "minimalist"
nitsuja wrote:
Is it possible to show "the" where it belongs at the start while ignoring it when sorting alphabetically?
by adding two names to each game: one for sorting, one for displaying. An overhead (database-, code- and maintenance-wise) I'd label "not worth it". Ultimately Bisqwit's call though, if there'll ever been a good time to do this change, it's now.
Tub, if you want SVN access to the site PHP code, that is doable :)
well, if you give me access, that'll put me in a position to do actual work. That's a position I'm not entirely comfortable with at the moment :p
honestly, if the combination of real life/university/job and lazyness factor permitted it, I would have finished the initial games-table half a year ago, like I initially wanted to.
This thread just lowered my lazyness-factor enough to do at least something, but time doesn't just appear out of thin air.
I was really hoping for "someone else" (i.e. you) to do the required php-changes, since you already know the framework, codebase and coding-style and you'll be more productive on that part of the work.
I'll be happy to support you as long as time permits it, but spending hours understanding and getting intimate with the codebase is only worth it if I'm going to spend a multiple of those hours working on it, and that's not going to happen anytime soon.
That being said, feel free to give me access just in case. It may or may not lead to further contributions, don't count on either.
By the way, how do you envision the transition period?
plan a)
- create a copy of the site (testserver)
- do and test all required changes on the testserver
- when done, redo the initial data (since there were new publications or changes in between) and copy to the real server
plan b)
- create the game-table & tools to maintain it
- disable publication for a while
- upload game-table & tools
- adjust all scripts to use the new database-structure, one by one
- when done, remove the redundant columns of the movie-table
- re-enable publication with an adjusted publication script
- with less of a hurry, add any less important tools and do maintenance/cleanups or additional tweaks as required
plan c) ???
