Cross Database Referencing?

[133794m3r]

Or is this a dream that i just had. I was sitting here setting up the databases and i wanted to keep all the things in the game that are going to be very strict and almost never changing in their own database away from all the things that are going to be changing via players playing. So anyway i'm attempting to or was going to attempt to reference a foreign key in a separate db within the main thing, if this is not possible then i'll just go in and semi-manually delete the item from the player's inventory's if it becomes severly bugged or something similar is found to happen with it.

I guess it's not really needed, but rather was just interesting for me at least to do.

[lolninja]

Hey, yeah it totally possible, you only need to have both database's on the same server, afaik you cant do cross server database joins.

Google found me this http://www.shawnhogan.com/2005/12/cross-database-join-with-mysql.html should help getting it all going right. The cool thing about this kind of implementation is you can optimize one of the tables for faster reading, and the others for a mixture of reading and writing, giving you the best of both worlds.

[133794m3r]

that's one of the reasons i was going to have two of them. The one database was completely optimized for read/writes since players are(hopefully) going to be playing it and thus changing up all the data. The other one was going to be for lack of a better way of saying it, containing various constants or well should be constants. It'd hold all the Skills, Mobs, Items, and such other things which should only be read and shouldn't be wrote except during development time but that'll be done at a local test server level. But it seems that i can't reference it like i was going to want to/wishing to do. Unless it's been updated to do such, now all that's left to do is to tr it out.

Also i was going to try mysql's site but since oracle's taken over, you can't even login nor register go Oracle's take over of mysql it's already worse than i imagined. I hope my signature helps it get overturned.

[mobeamer]

To be 100% honest, you don't need to create a new database to recieve optimization on reading.

A new table that is setup to be read from fast is as good a solution.

The only time a new database will be faster is when it is on a different server.

There are a number of reasons why this is but the most obvious is the CPU is only going to process one request at a time, regardless of how many databases exists on the system. It's going to open a table and read/write to it...if there is a join, those tables all get opened as well. When its done it closes them. It's the opening of the table that slows the database down (especially when its opened for writing). There is usually also a limit to how many times the same table can be opened at once for read only. (informally known as connection pooling). In addition to the CPU constrant usually the database sits on a harddrive, the harddrive can only read one database at a time and deliver the contents to CPU...so another bottle neck.

Anyway, if you want to see a real increase in speed, have a table that has read only information in it. This table is updated once every hour (or never). Never join this table with a table that is written to. For even more performance move that table to a different server.

I believe the technical jargon for this is data warehousing or data replication. It's also comes into play when you speak about data caching.

*shrug* I wrote to much but feel like sharing today.

[JGadrow]

The points listed by mobeamer are correct. However, in order to prepare for the possibility of a shift to separate servers, you will need to implement the items in separate databases anyways. So, keep it up. You will still see a minor performance gain by switching your table engine on the read-only tables but there is only so much you can do to optimize your database load without moving to a multiple server setup.

Something to consider is how the components of your actual game (or application as it applies equally) work together. For instance, if you're going to be implementing multiple games but don't wish to force your users to create multiple accounts for those games, you'll want to implement a separate "accounts" database so that it may eventually be housed on a separate authentication server or whatever the future might hold.

[133794m3r]

one of the main reasons i did do it like how it is atm was to try to not do any repeating of the information in them, and also it was to make my life a little bit easier. As JG said something like having an authentication server was always interesting to me to do. And at the moment i was planning on in the future sometime opening up a second so called server for the game world, it'd be set in another time but i was going to let players play on either one freely. They could only transfer their monetary wealth, level, and skills back and forth between them though. Also a player could start on either one if so wished. It was part of my current plan to allow players to play their character in two different "worlds" so to speak down the road if the game became popular enough to support it. If it does not, then so be it, i started this whole thing to learn another language and polish my web design, javascript, php, and database designing skills. It's mostly being done as a learning experience but also to make something that's fun. And interesting chat example there .

Basically right now i'm working 'Haphazardly" so to speak, i'm making the front page which'll set up the style of the pages, and i'm also attempting to make the developer panel/admin panel. And this is to simply make my job easier once i start implmenting the various quests and such other things i've designed into the game.