Table of player offices

[Chris]

The question is how to design the 'offices' table for this.

There are several kingdoms, each kingdom has a king (player) and citizens (other players). The king can assign offices to citizens, also some offices allow assigning other offices (like the king assigns the Captain of the guard and then the captain assign guards). Each player can have up to 3 offices active (if a player has more than 3 he has to decide which 3 make active).

What I will probably need:
- name of the office (simple string)
- country_id (define the country/kingdom)
- owner_id (the player id of the player who holds the office right now)
- assigner (some sort of identifier of the office (not player) that is allowed to assign this office (like Guard office will have the assigner set as the Captain), all offices with assigner=0 are directly assigned by the king
- status (if the office is active or not, max 3 can be active at once per player)

Now, the biggest dillema is how to make the office identifier... I want new offices to be added easily and at least half automaticly. Maybe some sort of "type" which define which kind of office is it and type+country_id could be used to identify the office uniquely?

To make it more complicated there will be also cities (part of the kingdom), so in some cases there will be identical sets (groups?) of offices within the same kingdom that are duplicated for all cities in the kingdom (like each city would have a Mayor who can assign lower city offices). So city_id for some offcies would be needed as well...

Any thoughts? How would you make this?

[AltarofScience]

jobid|jobtypeid|countryid|cityid|assigner|

the job id is unique, it will be the primary key.
the job type id is the type of job, this allows the job to be assigned.
the country id identifies the country where the job is, this is for checking if a job is available in the country.
the city id defines the city location of the job, for jobs that have caps per city.
the assigner can be an integer or a null, could be 0 instead of null, doesn't matter.
players can assign any role with the job type id that corresponds to their assigner.
that table describes each job

then you need a table for assigned jobs:
playerid|jobid

the player id obviously identifies the player.
the job id obviously identifies the job.

you will be using the COUNT() function to establish whether the player can receive a new job.
similarly you can use the COUNT() function to establish the availability of the job in the kingdom and the city.

[Chris]

AUTO_INCREMENT PRIMARY jobid/officeid is of rather low use in this situation, it is in random order (I will be creating offices at various stages of development so new one will pop up in the middle), not connected to the country_id (offices will be created partially by the king request so again the order is unpredictable). Actually, it is useful only for assigner_id (which would really benefit from having a single unique INT to identify an office).

Anyway, I would not want to create an index for jobid field, since I simply almost never will be using that index (only to check the assigner_id which is very rare), it would be a waste as an index. I guess I can't make an AUTO_INCREMENT field without making it an index, correct?

I wonder about some compound identifier, like country+city+type+officenumber (to indentify which 'slot' of the office it is within identical offices, like Guard office below).

Code: [Select]

function createOffice($country,$city,$assigner_id,$name,$type,$officenumber=NULL) {... return officeid;}

// Country
$country=6;

// Central government offices
$city=0;
createOffice($country,$city,0,'Inspector',99);

// City X offices
$city=5;
$assigner_id=createOffice($country,$city,0,'Captain',100);
createOffice($country,$city,$assigner_id,'Guard',101,1);
createOffice($country,$city,$assigner_id,'Guard',101,2);

// City Y offices
$city=6;
$assigner_id=createOffice($country,$city,0,'Captain',100);
createOffice($country,$city,$assigner_id,'Guard',101,1);
createOffice($country,$city,$assigner_id,'Guard',101,2);

[Monatar]

AltarofScience is just about right. Every table needs a unique ID, so simply create one with auto_increment. Creating a combined ID with a lot of data in it is inefficient. You can use secondary indexes for often used searches, like playerID.
I suppose you already have a countries-table and a cities-table (containing coutryID).

The offices-table would contain:
- officeID (auto_increment primary key)
- playerID (or characterID if you separate players from characters they play)(secondary index)
- officeTypeID (denotes type of office, like king, mayor, guard, etc.)
- countryID (country in which the office is located)
- cityID (city in which the office is located, if appropriate)
- assigner (officeID of the office that assigned this office to the current player)
- creator (officeID of the office that created this office)
- superior (officeID of the office this office is directly reporting to)
- status (vacant, inactive, active)
- name (of this particular office, like 'guard of the second cohort of the city guard of SomeTown')

Depending on how often you need it you could create other secondary indexes.
You could combine kingdomID and cityID into one field if you like.
I named separate fields for assigner, creator and superior for greater flexibility in office structure and assignment options. In your design they will all be the same.

There also would be an officeType-table. This table contains data for all offices of the same type (like kings, mayors, guards):
- officeTypeID (autu_increment primary key)
- officeName (name of this type of office, i.e. 'King')
- officeData (data defining what an officer of this type can and/or is obliged to do. Privileges and obligations)
- areaOfEffect (kingdom, city or any other area type you may define later on. Not a specific location!)

This table is particularly useful if you would want to create new types of offices later on (tax-collector, judge) and if you want to alter the privileges and obligations of some type of office (like when the king can assign (or fire!) a captain of the city guard directly instead of having to instruct the mayor to do so).

This structure allows for maximum flexibility in your game specifics, uses minimal database space and is pretty search-efficient.

[Chris]

The combined ID can be just a simple INT. Assuming we have 100 countries, 1000 cities, 100 types of offices and max 100 offices of each type (officenumber) we could make it: office_id = country+city*(100)+type*(100*1000)+officenumber*(100*1000*100). This way I can recreate the officeID via algorithm and I can still have just one INT as unique indentifier for the purpose of assigner_id field...

As for indexes, I think I will make just one, for player_id (owner_id). And maybe for country_id since people might want to look frequently at the list of available offices in the kingdom.

[BlackScorp]

tbh this "office_id = country+city*(100)+type*(100*1000)+officenumber*(100*1000*100)" is not Software Design.. why just dont make a nice Table with Relationships and read nessesary data with well Queries with Joins, cache the results in memcache and such stuffs to improve the performance.. use Views to store some data but please dont do stuffs like this ID.

you Assubmin that you have 100 countries and 1000 cities and stuff.. but what if not? and how will you make an algorithm with 3 Variables? this is a really bad idea.. i would make Groups with types

lets say you have x kingdoms, each kingdom has x countries, each country has x cities, each city has x officegroups, each office group has x offices

so anything you need is id, name, type, parent_id the parrent id is the id of the same table , so if there is no parent id, your group is root in the group tree you dont need to seperate it, you can declare the type e.g. office, citie,county ,officegroup,officememeber. and you can use Group by and order by statments, to get that tree..

[Barrikor]

office_id = country+city*(100)+type*(100*1000)+officenumber*(100*1000*100)

Would work fine if you can make sure that there's no collision with different jobs ending up with the same ID.

You'll probably need to be able to store all that stuff independently somewhere else anyway though.

[Chris]

Done, thanks.


BTW, recalculating 1D array to multidimensional array is a proper software design and is used extremely often, that's how it was frequently done in C and in ASM you don't even have other options (malloc() just allocate a linear block of memory and you have to recalculate the 2nd dimension by hand). You just got spoiled by the relational databases

[BlackScorp]

Done, thanks.


BTW, recalculating 1D array to multidimensional array is a proper software design and is used extremely often, that's how it was frequently done in C and in ASM you don't even have other options (malloc() just allocate a linear block of memory and you have to recalculate the 2nd dimension by hand). You just got spoiled by the relational databases

office_id = country+city*(100)+type*(100*1000)+officenumber*(100*1000*100)

that is not recalculating 1D array to multidimensional array, this is making a value of some other values.. its similar to $office_id = md5("test"); you will never be able to recalculate it back. and you cannot compare arrays with databases tbh..

[Chris]

Done, thanks.


BTW, recalculating 1D array to multidimensional array is a proper software design and is used extremely often, that's how it was frequently done in C and in ASM you don't even have other options (malloc() just allocate a linear block of memory and you have to recalculate the 2nd dimension by hand). You just got spoiled by the relational databases

office_id = country+city*(100)+type*(100*1000)+officenumber*(100*1000*100)

that is not recalculating 1D array to multidimensional array, this is making a value of some other values.. its similar to $office_id = md5("test"); you will never be able to recalculate it back. and you cannot compare arrays with databases tbh..

It's normal 4 dimensional array, of course you can recalculate it back.

Maybe a simplier example. You have allocated 10000 bytes of memory starting at byte 15000 ending at byte 25000 (decimal). You are making an RPG, how do you store a 100x100 2D map there?

maxx=100; maxy=100; // (we don't need maxy, just for consistency)
byte_in_memory=x+y*maxx;  // (x is incrementals of 1, y is incrementals of 100, as long as x is within 0-99 range there will never be a conflict), so the x=5,y=3 will be in byte 15000+305, and this way we recalculated 2D (x,y) to 1D (offset).

Making it 3D would be byte=x+y*maxx+z*maxx*maxy;

Of course if you ever go out of range (like suddenly deciding the map is 200x200 without changing the code everywhere) will make you toasted

[Nox]

Yea, I agree with Chris, why make it simple, clear and proper when you can make it obscure and difficult

Why, *WHY*?
No performance gain (- data transfer + recalculations, table search time stays the same), negligible hdd/memory gain, no readability, manual changes impossible, design change invalidating all the IDs, rigid and fragile system, more difficult querying, need for an array of support functions (ID recalculations, getting range for country/city/type/office etc.)

I want new offices to be added easily and at least half automaticly.

This system you made up is exactly what you do not want in that case, adding new content will be very difficult to say the least

[BlackScorp]

i store my map in a database, 100x100 would mean 10000 rows, but i store only not default parts of the map. usually you have over 50% of your map with a default tile. so you can save the amount of rows if u setup a default tile. then i dont load the whole map, just a part , that actually need to be viewed. after moving the viewport, i just load the new tiles which are needed. so only the initial load of the map costs most resources.

now your example
office_id = country+city*(100)+type*(100*1000)+officenumber*(100*1000*100)

lets say, we have country_id 2, city 4, type is 5 officenumber is 6. so the office id would be
office_id = 2+4*(100)+5*(100*1000)+6*(100*1000*100)
office_id =60500402.

Well now explain me , how will you get from this number the country_id, the city, the type and officenumber?

[Nox]

the same as with array index or conversion of radix, currency (I mean x gold, y silver, z copper) etc.

officenumber = floor(office_id / (100 * 1000 * 100));
office_id -= officenumber * (100 * 1000 * 100);

type = floor(office_id / (100 * 1000));
office_id -= type * (100 * 1000);
and so on

it theoretically is possible ... but I don't see any advantage given the range of disadvantages it brings ... which mainly stems from it being ID and unlike the radix or currency the logical semantic is in those split items, not the compound number (we can very well work with 123551 copper, splitting it into gold, silver, is just a matter of presentation - whereas the office_id has no meaning), and the compound number consists of not so related items (when compared to those other examples)

btw if it's about performance then realize that performance for querying for people in the same city, type or country will go down, as it will no longer be query for specific ID, but query for ID range office_id > x and office_id < y

[Chris]

why make it simple, clear and proper when you can make it obscure and difficult

You are forgetting that the whole 1D >xD is just ONE LINE OF CODE Compare this with joins, views, whatever. It is so overwhelming simplier.

Plus joins are far more obscure compared to the standard basic array math, but I guess the tastes here can differ

lets say, we have country_id 2, city 4, type is 5 officenumber is 6. so the office id would be
office_id = 2+4*(100)+5*(100*1000)+6*(100*1000*100)
office_id =60500402.

Well now explain me , how will you get from this number the country_id, the city, the type and officenumber?

maxx=100; maxy=1000; maxz=100;

x=2; y=4; z=5;
byte=x+y*(maxx)+z*(maxx*maxy)
byte=504002

Reverse:
x=(int)(byte%(maxx*maxy)) = 2
temp=(int)(byte/(maxy)) = 504 ; y=(int)(temp%(maxx)) = 4
z=(int)(byte/(maxx*maxy)) = 5



(note: in my entire coding life I never needed to use the reverse, you basicly always need it one way)

[arai]

Now remember everyone... Chris' game is his game, and we're just here to offer advice on how he can solve some of the challenges he's been running into.

Now that that's out of the way. Chris, be sure to comment your code with the following lines:

• You are forgetting that the whole 1D >xD is just ONE LINE OF CODE  Compare this with joins, views, whatever. It is so overwhelming simplier.
• Note to self:  When I'm in here in 3 months trying to figure out what the hell I was thinking, package up the entire mess and post on thedailywtf.com

That is all

[Chris]

Now remember everyone... Chris' game is his game, and we're just here to offer advice on how he can solve some of the challenges he's been running into.

Well, 5 or 6 posts above I posted "Done, thanks." since my dilemma was solved and I implemented it. I assume that after that this topic turned into a debate about various things (which happens quite often on this forum).
But I do fully agree with the concept that people should focus on making *ME* happy as the first priority (and I would go as far as not limiting it only to this topic)