Forum BigDB what's the preferred structure?

Discussion and help relating to the PlayerIO database solution, BigDB.

what's the preferred structure?

Postby Tsuken » June 9th, 2010, 10:07 pm

I'm not used to using this type of database, so I need some confirmation on the preferred structure.

Let's say you have the Universe, which consists of an number of galaxies, which consists of a number of solar systems, which consists of a number of planets, which are owned by players.

I could go the basic way with 4 different tables, like:
Universe
id_universe

Galaxy
id_galaxy
id_universe

SolarSystem
id_solarSystem
id_galaxy

Planet
id_planet
id_solarSystem

But considering this database can store objects and arrays, I could make it with 1 table (see image attachment):
Table Universe has an array called galaxies, which contains all its galaxies, which each contains an array called solarSystems, which contains all its solar systems, which each contains an array called planets, which contains all its planets. See attached image.

I would say it's better to split it up into multiple tables and use the ids, as you would in a normal database. But is my 2nd example a serious possibility? I would expect it to crash the server, or would it work just fine? And even if it would just work fine, what would you suggest?

(by the way, feel free to laugh, I know it looks ridiculous :))
Attachments
universe.jpg
universe.jpg (105.27 KiB) Viewed 13064 times
Tsuken
 
Posts: 41
Joined: January 26th, 2010, 7:54 pm

Re: what's the preferred structure?

Postby Benjaminsen » June 9th, 2010, 11:29 pm

There is an upper limit to how large each object is allowed to be, thus your proposed model breaks down quite fast. Additionally your model does not work with index and you also totally loose all the cool features that the access system gives you.

tl:dr: Don't put everything in one object.
Benjaminsen
.IO
 
Posts: 1444
Joined: January 12th, 2010, 11:54 am
Location: Denmark

Re: what's the preferred structure?

Postby Henrik » June 10th, 2010, 10:13 am

If you never change which solarsystem a planet is in, and which galaxy a solarsystem is in, etc, the simplest model is just one table, with objects like this:

Code: Select all
planet_id = {
    owner_userid = ...
    solarsystem_id = ...
    galaxy_id = ...
    universe_id = ...
    name = ...
    properties = {
        ...
    }
}


And then you make one index over that table on these properties: universe_id, galaxy_id, solarsystem_id. With that index you can then easily look up all planets in a universe, all planets in a galaxy, or all planets in a solarsystem. If you add an index over owner_userid you can look upp all planets by owner.

Now, this model won't allow you to easily move all planets in one galaxy to another for example, since you would have to load all those planets and change them and save back. It's also harder to get a list of all solarsystems in a galaxy as well, but you could make another table where you store these objects:

Code: Select all
"universes" = [id, id, id, id, ...]

universe_id = {
    name = ...
    galaxies = [id, id, id, id, ...]
}

galaxy_id = {
    name = ...
    solarsystems = [id, id, id, ...]
}

solarsystem_id = {
    name = ...
}


Remember, there are no constraints on tables in BigDB, and given that the number of these objects is fairly low, and that you won't need an index, you can easily just put them all in the same table. The only thing you need to think about is making sure the keys are unique, but you could just prefix the integer id of each object with "u" for universes, "g" for galaxies, etc.
Henrik
.IO
 
Posts: 1880
Joined: January 4th, 2010, 1:53 pm

Re: what's the preferred structure?

Postby Tsuken » June 10th, 2010, 12:09 pm

Thanks a lot for the replies. Very helpful.
Tsuken
 
Posts: 41
Joined: January 26th, 2010, 7:54 pm


Return to BigDB



cron