Forums

Sega Master System / Mark III / Game Gear
SG-1000 / SC-3000 / SF-7000 / OMV
Home - Forums - Games - Scans - Maps - Cheats - Credits
Music - Videos - Development - Hacks - Translations - Homebrew

View topic - DataBase model draft

Reply to topic
Author Message
  • Site Admin
  • Joined: 08 Jul 2001
  • Posts: 8644
  • Location: Paris, France
Reply with quote
DataBase model draft
Post Posted: Sun Feb 15, 2004 12:25 pm
I am ... very bad ... at DB design. I should submit those draft to someone who is experienced with DB design.

If you want to have a look:
http://wip.smspower.org/tests/db



I tried to make things simple in many area.

I just noticed that I messed up the "game_document" table. This table only purpose is to link games to their documents to be able to view documents using a standard game URL.
Something like:
http://www.smspower.org/view.php?game=xxx?document=review1
instead of something like:
http://www.smspower.org/doc.php?id=8r723423843f7sd87f8sd

Else, document will be all-purpose pages to store articles (eg: description of console systems, peripherals, article about a set magazines, developer interview, etc...).

Of course, any question/comments/correction/tips is welcome.
  View user's profile Send private message Visit poster's website
  • Site Admin
  • Joined: 08 Jul 2001
  • Posts: 8644
  • Location: Paris, France
Reply with quote
Update (draft 04)
Post Posted: Sun Feb 15, 2004 12:35 pm
Added some contents and removed wrong relationships between game & document.

All files at:
http://wip.smspower.org/tests/db
(xml model to be loaded using DBDesigner, and perhaps other software?)

Draft 04:


  View user's profile Send private message Visit poster's website
Grass-eatin'me
  • Guest
Reply with quote
Comments:
Post Posted: Mon Feb 16, 2004 2:46 pm
System table: not needed. all system info can be entered into PHP scripts. You just need a systemID in the game.

all "urls" and "paths" aren't needed. those should be made in the PHP script, be using the gameID, or any other ID.

screenshots.. do you really need comments to those? the easiest way would be a loop script which automaticly showed all images in (f.ex) /screens which start with ID and ends with a double int, all the way till it doesn't find anything anymore.

type could also be added that way, say wb3_01_01.jpg would be type 1, image 1. Small comments: wb3_01_01_main_menu.jpg

What is the technote table about?

I also suggest to use .txt files for the tips and/or other texts. These would be named in the same manner as above.
 
  • Site Admin
  • Joined: 08 Jul 2001
  • Posts: 8644
  • Location: Paris, France
Reply with quote
Re: Comments:
Post Posted: Mon Feb 16, 2004 4:10 pm
Quote
> System table: not needed. all system info can be entered
> into PHP scripts. You just need a systemID in the game.

Yes. That's why the system table is not linked to anything now. I'll remove it.

Quote
> all "urls" and "paths" aren't needed. those should be made
> in the PHP script, be using the gameID, or any other ID.

For ROM url, it could be removed and computed by the script.
Not so easily for screenshot (see below).
For scans, we will sometimes want to include some subtle variation information into the filename.

Quote
> screenshots.. do you really need comments to those?

Yes. I wanted to be able to categorize them.
My idea was to use the enum as a small hack: one enum value would be to tell the script to add a standalone title/comment.

So we could have a table filled like that:

type=title, comment="Level 1"
type=screenshot, path="...", comment=""
type=screenshot, path="...", comment=""
type=screenshot, path="...", comment=""
type=screenshot, path="...", comment=""
type=title, comment="Level 2"
type=screenshot, path="...", comment=""
type=screenshot, path="...", comment=""
type=screenshot, path="...", comment=""
type=screenshot, path="...", comment=""
etc.

Path should be simplified (as you pointed it out), we should only store a screenshot number (not necessary the same as the sorting id) or a postfix (like storing "test" will compute wb3-test.gif").

Quote
> type could also be added that way, say wb3_01_01.jpg would
> be type 1, image 1. Small comments: wb3_01_01_main_menu.jpg

It's tricky to put info/comments in filename and not very good looking.

(Also I'm sure we'll avoid using JPG for game screenshots ;)

Quote
> What is the technote table about?

May contains: extract of code/disassembly, tricks used, hacking guidelines, game variable addresses, etc.

Quote
> I also suggest to use .txt files for the tips and/or other texts. These would be named in the same manner as above.

Why not directly storing them into the DB ?
Although, I agree that long documents (FAQ, Walkthrough) should be kept downloadable and will be stored as separate files.

Thanks for your inputs, I greatly appreciate it.

Does the rest looks ok? I made that without much theorical background so I'm not sure if it's correct.
  View user's profile Send private message Visit poster's website
  • Site Admin
  • Joined: 08 Jul 2001
  • Posts: 8644
  • Location: Paris, France
Reply with quote
Update (draft 05)
Post Posted: Tue Feb 17, 2004 9:12 pm
Removed prefixes from field names (they are useless since you can specific containing table in SQL query and avoid confusion). However some fields names were automatically disallowed by my model editor: "comment", "length", etc... So I tweaked names a bit to avoid using those seemingly reserved words...

Added "screenshot_number" field to screenshot table.

Changed various other fields types.

I'd like to try implementing a first quick & dirty setup to list games and their basic content (game & game_versions tables).

All files at: http://wip.smspower.org/tests/db
(xml model to be loaded using DBDesigner, and perhaps other software?)

Draft 05:


  View user's profile Send private message Visit poster's website
  • Site Admin
  • Joined: 19 Oct 1999
  • Posts: 14688
  • Location: London
Reply with quote
Theoretical background
Post Posted: Wed Feb 18, 2004 9:52 am
Quote
> Does the rest looks ok? I made that without much theorical background so I'm not sure if it's correct.

As I'm halfway through my databases course I can tell you this:

Whatever works best is what's best. There are always refinements you can make that will make it theoretically more safe (usually, splitting tables to avoid redundancy or dependencies) but practically worse (too many tables to JOIN for every lookup).

Maxim
  View user's profile Send private message Visit poster's website
Grass-eatin'me
  • Guest
Reply with quote
New comments.
Post Posted: Wed Feb 18, 2004 2:33 pm
Are there any reason to have more than one document table? From my point of view, tip, technote and document can be merged into one. By, for example, using a enum wich describes the content of the document.

Also, the bools at game can be removed, and replaced by single SET for periphials.

Also, shouldn't the screenshot table be connected to the version table instead? That would allow for different screenshots for the different versions.

The screenshots and scan tables can also be merged. Again, with an enum. Manual scans could also fit into such a table.

What is the game_media table for?

Also, I don't quite understand the document tables structure.

I would've designed a draft like you did, if the app didn't crash on me all the time.

I might start to develop a sample app once I get on a proper development platform. (Linux or W2k, not W98 as I'm on right now, heh)
 
  • Site Admin
  • Joined: 08 Jul 2001
  • Posts: 8644
  • Location: Paris, France
Reply with quote
Re: New comments.
Post Posted: Wed Feb 18, 2004 4:38 pm
Quote
> Are there any reason to have more than one document table? From my point of view, tip, technote and document can be merged into one. By, for example, using a enum wich describes the content of the document.

Tips and technote are currently table with same columns, but I separated them since they may differs in the future. For exemple, the tips table could get improved to contains Pro Action Replay / Game Genie codes, etc. I believe it's better not to merge them for that reason (although I understand it is tempting...). Also note that tips & technotes will be available from different pages (it is better only SELECTing within cheats when the user is looking for cheats...).

As for documents, they are meant to get more complicated in the future. While tips & technotes entries are meant to be short and included within a page, a document IS the page, and the document system could support various other features in the future (variable replacements, keywords addition per page, etc...).

Quote
> Also, the bools at game can be removed, and replaced by
> single SET for periphials.

Right. I just learned about SET thanks to this suggestion.
(ref: http://www.mysql.com/doc/en/SET.html)
I suppose that SETs are slighty more tricky to reuse on the user (PHP) side, but this is definitively the appropriate structure here.

Quote
> Also, shouldn't the screenshot table be connected to
> the version table instead? That would allow for different
> screenshots for the different versions.

Theorically yes. But I felt that in 90% cases, different versions are minor and do not reflect in-game, so we would have most screenshots shared. What I could do is to include an optional version connection, thought.

Quote
> The screenshots and scan tables can also be merged.
> Again, with an enum. Manual scans could also fit into
> such a table.

Screenshots are more specific. But all kind of scans could be merged (box, manual, and even other cartridge scans perhaps).

Quote
> What is the game_media table for?

Advertisements, mainly. On user side, probably we will have one "Media" pages with ads, music (VGM), perhaps scans, etc.

Quote
> Also, I don't quite understand the document tables structure.

What do you don't understand?
It's preliminary right now, I think it's not the first thing we'll use. When we will start to have real use of documents (reviews, interviews, articles about the machines, peripherals) we will probably build this table.

Quote
> I would've designed a draft like you did, if the app didn't crash on me all the time.

If you find a better one, I'm welcoming it. :)

Thanks
  View user's profile Send private message Visit poster's website
  • Site Admin
  • Joined: 08 Jul 2001
  • Posts: 8644
  • Location: Paris, France
Reply with quote
Update (draft 06)
Post Posted: Thu Feb 19, 2004 10:19 am
All files at: http://wip.smspower.org/tests/db

Draft 06:


  View user's profile Send private message Visit poster's website
Reply to topic



Back to the top of this page

Back to SMS Power!