Out of Print Archive forum
November 23, 2017, 08:11:53 PM*

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
News: SMF - Just Installed!
Advanced search  
Pages: [1]
  Print  
Author Topic: Queryable Database  (Read 2072 times)
grandta13
Newbie
*
Posts: 6



View Profile Email
« on: January 19, 2016, 11:34:58 PM »

I was wondering if you would be interested in implementing a queryable database that would allow users to do searches for specific games, magazines, or issues. I've created a mock-up database to demonstrate the concept (it's not fully optimised, and it only has sample data at the moment).

Say you wanted to search for all issues of magazines that included Sonic 2, you would get a result set including the following:
+----------------------------+---------------+------------------------------------------------------------------------------+
| Magazine                   | Issue         | Issue URL                                                                    |
+----------------------------+---------------+------------------------------------------------------------------------------+
| GameFan                    | Vol.1 Issue 1 | http://www.outofprintarchive.com/catalogue/gamefan/GameFan1-1.html           |
| GameFan                    | Vol.1 Issue 2 | http://www.outofprintarchive.com/catalogue/gamefan/GameFan1-2.html           |
| Mega Drive Advanced Gaming | Issue 1       | http://www.outofprintarchive.com/catalogue/megadriveadvancedgaming/MAG1.html |
+----------------------------+---------------+------------------------------------------------------------------------------+


There are other benefits that a database system could provide, such as the ability to search for specific magazines, search magazine by subject (e.g. 'Sega').

There is also a lot of room for additional features that could be implemented based on how much you want the database to handle.

I've attached the script I used to create the sample database (MariaDB MySQL) to this post, and I'm also going to PM the administrators with information on how to access a sample database that is already set up (for testing).

P.S. The script also includes views for searching for specific games and displaying all issues in the database.

EDIT: Sorry, all the attachments are copies of the same file; there were some issues and I didn't realise the file was attached three separate times.
« Last Edit: January 20, 2016, 01:03:31 AM by grandta13 » Logged
meppi64
Administrator
Sr. Member
*****
Posts: 480


EDF! EDF! EDF!


View Profile Email
« Reply #1 on: January 20, 2016, 05:20:35 AM »

That certainly looks very interesting to me.
In the past we've looked into getting something similar, although not quite as intricate set up, but it never really went anywhere.

For me personally, there are two big problems when it comes to this.
One, I have no idea how to create it or implement it as I've never used anything like that before.
And two, I'm afraid that it looks like it takes up quite a lot of time to put all that data together, along with the linkups and everything, so it would take away from the limited time I have to work on the magazines themselves as well as write the site updates.

Not that I'm against it, far from it in fact.
Just realistically speaking, I wouldn't be able to do this on my own.
Logged
grandta13
Newbie
*
Posts: 6



View Profile Email
« Reply #2 on: January 22, 2016, 04:20:09 PM »

That's perfectly acceptable. I would be willing to do a lot of the work. I can't promise a timeframe, as I would have to treat this as a side project.
(GoDaddy includes support for hosting a MySQL database).
If you are willing to go through with this, then I would like to ask a few more questions.


  • Do you have the data stored anywhere already (such as text documents, spreadsheets, or anything else)?
  • How much would you like to allow the user to search for? (This can range from as little as magazine names to a specific as an issue's publication date or an editor's name.)
  • Are there any other concerns/questions that you would like addressed?
Logged
meppi64
Administrator
Sr. Member
*****
Posts: 480


EDF! EDF! EDF!


View Profile Email
« Reply #3 on: January 23, 2016, 05:05:59 AM »

The only info on the magazines I have written down is in the release pages of the magazines on the site.

As far as being able to look for certain things, I'll leave that up to you.
Love the idea of being able to look for articles from certain writers for instance though as well as editors.

Also, this might sound really dumb, but how would this be implemented into the site?
Would it be possible to add a search button on the menu bar or something and get the info that way, or would there need to be a specific link to the database, where the search box and all the results would be?
Like I said, I really don't know anything about this part.
Logged
Nreive
Administrator
Full Member
*****
Posts: 182


View Profile
« Reply #4 on: January 23, 2016, 07:36:49 AM »

A great idea and something I've been longing to learn more about myself. If I can help in any way, let me know.
Logged
grandta13
Newbie
*
Posts: 6



View Profile Email
« Reply #5 on: January 25, 2016, 11:39:52 AM »

I'll be honest, most of my experience as a DBA is isolated to a LAN-Server environment; I don't have a lot of experience with Web integration. I'm always expanding my repertoire, so I am willing to take the time needed to figure out anything I don't already know (I'm currently focused on learning ASP, among other things).

I would like to ask a few more questions to help determine how to proceed.
  • Do you want the entire site to be database-driven (see this link if you're not sure what that means), or do you want to keep the static layout you have and just add search engine?
  • Are the names of systems consistent across magazines? (i.e. Is 'PSone' the only name you use, or are 'PS1'/'Playstation' used in some instances?)
  • Do you differentiate between Windows/DOS/Mac games?
Logged
meppi64
Administrator
Sr. Member
*****
Posts: 480


EDF! EDF! EDF!


View Profile Email
« Reply #6 on: January 25, 2016, 02:43:44 PM »

That first part is a little moonspeak to me, but I'll try to answer the second part. Wink

I've just skimmed the page about database-driven websites since I just got home from work right now.
But from my understanding, I think going with the regular HTLM one would be the easiest way since the data on each page, once it's written usually doesn't change much at all, unless there are errors which might be corrected later on.
Not sure if there are any other major advantages of a database-driven site?

For the most part, the names should be uniform, although certain versions might be used when game titles ended up being too long for the table to hold. Might have to go through them and make them all the same, which shouldn't be too bad I guess.

When I put up the micro part of the site, I didn't differentiate between them since the bulk of magazines that I own hardly even mention PC games. I played quite a lot of DOS games in the 80's, but once the NES  came out I pretty much switched to consoles till now. So I don't have any PC magazines myself. :-/
Logged
grandta13
Newbie
*
Posts: 6



View Profile Email
« Reply #7 on: January 25, 2016, 11:26:20 PM »

The primary benefit of a database-driven site would be reduced management in the long run; you would only have to have one copy of each type of page (effectively a template), and the content is filled in dynamically by the database. The downside to this, however, is that every page view would ping the webserver and the database, which would result in higher bandwidth consumption.

The main reason that I brought up the desktop OS thing was that I noticed some games in CVG being listed as 'PC CD-ROM', and was unsure if that is just used to refer to Windows/DOS games, or if it extended to other platforms.

Also, since the data isn't quite consistent, it looks like data will have to be scraped and reviewed on a page-by-page basis. Using import.io, I've created an API that pulls games from each page with reasonably high accuracy; the main issue are the minor inconsistencies, which must be accounted for when they are encountered.

P.S. Do you or any of the other administrators understand SQL (the language used with the database)?
Logged
Nreive
Administrator
Full Member
*****
Posts: 182


View Profile
« Reply #8 on: January 26, 2016, 04:29:25 PM »

I have lightly touched SQL with the likes of WAMPserver but haven't fully got into it as yet. My own experience of DB come from the software package side of things (e.g. Microsoft Access). When you say a database-driven website, do you mean in a similar style to the likes of Amiga Magazine Rack (http://amr.abime.net/)?

Most of the PC content on OoPA is from myself, but I don't recall any Mac content. I think that was put in along with the PC section due to the little or no content there is. As for the PC differentiation, I think listing Windows or DOS games under PC would be the way to go. I believe CVG stated games as PC CD-ROM during that time as it was a leap in media storage and they wanted to make that distinction from the older Floppy Disk games. All PC games in my eyes though whether it is FDD, CD, DVD.
Logged
grandta13
Newbie
*
Posts: 6



View Profile Email
« Reply #9 on: January 27, 2016, 03:34:21 AM »

When I say database-driven, I just mean that you don't have to manually enter the information into each page; you enter the information into the database, and the page is created automatically with that information. To the end user, the site would remain mostly the same; the main difference is in administration. The other benefit is that if you decided to change your format to a different style, you could potentially do that more flexibly, since you would only have to design the new pages once, as opposed to manually redesigning every page.

The two main downfalls to a database-driven website are increased server-cost per view, and the site would have to be recreated to work with the new system. In theory, that shouldn't be too much of an issue, but in practice we may encounter unforeseen technical issues.

I plan to implement database creation in multiple phases:
  • Collect Magazine data
  • Collect Issue-specific data (Issue Number, editor, date published, etc.)
  • Collect Game data per-issue
  • Collect data that is stored within the magazine scan* (article writer, article names/descriptions, etc.)
*This should definitely wait until the very end, and should be treated as a feature that would be nice to have implemented, but may never be, due to the sheer amount of work required to weed through all those scans by hand to find information.

The main reason I was asking about the 'PC CD-ROM' thing was that I am using a scraper to pull the data automatically from the pages, and I wanted to find out if I needed to store all CD-ROM games in one category, or if I may have needed to include additional categories (such as 'Mac CD-ROM', for example). If there is currently no distinction, it would be best for me to store the data as just 'PC CD-ROM'. Whenever the game data is collected, we can move on to specifying which platform(s) the game applies to.

P.S. I typed this post after staying up all night, so there may be some typos or something that doesn't make sense. If you don't understand something I wrote, ask; I have no issue with giving any clarification needed (provided that I still understand what I typed when I wake up).
« Last Edit: January 27, 2016, 05:38:04 PM by grandta13 » Logged
Pages: [1]
  Print  
 
Jump to: