Multi Campaign DB

[Mirviriam]

I'm trying to figure out if we should create a cross-link table for campaign_ID & the:

people_campaign_ID
domain_campaign_ID
province_campaign_ID
holding_campaign_ID
alliance_campaign_ID

Maybe some others. Otherwise we have to embed the campaign_ID in every table.

[Dyark]

what is campaign id ??

[Mirviriam]

We could call it whatever we wanted. The purpose is to separate different games of birthright being played. With it we can have several games going on with various amounts of players & DM's ...

My DB envisions having a person sign up on the web page, then choose "Create New Game". He then sets which continent & domains are available for players, designates NPC domains & domains/realms(countries) which will not be allowed in the game. After that the new DM will choose if he wants open sign up (publicly available, handled automatically) or if the DM needs to send invitations for players to claim a realm/domain.

I will eventually like to add DM preferences like, how long out of game players have to do their turns (1 month always bored me), variant rules off wiki & in BCRS, an army fighting script etc.

To do that we need an ID that flags the data in certain tables as part of one particular game.

[Dyark]

Certain ideas were taking, like a RP buffer to counter action, war is either script driven of manually or both.

Turn are per initiative in which if you don't take your action by X number of days, it means you don't do anything (of course major campaign with 50 and over player needs other kind of rules to fix that)

For me adventure will pass by other tools that I use.

I am building a multi use database for different campaign ( the basic database never change only new table are created for new campaign )

[Mirviriam]

I never thought of using a separate table for each campaign. I suppose we'll never see the number of users that would make the table's over head catch up to sql seek time? Is that why you're doing it?

[Dyark]

just wandering why you seek the number of user

What i mean is each new style of campaign is separate, on a single campaign, you can have many dm (1 table)

[Arius Vistoon]

I am building a multi use database for different campaign ( the basic database never change only new table are created for new campaign )

euh... don't create new table for each campaign (never). If you want multi-campaign use, create a link in the appropiate table (in high header of it for better package) or alternate solution, create a new databse with a connect to the "root" database" (but never create table on the fly, when a database is created, it will can change its structure only for exceptionnel thing like a new feature. a new campaign is not a new feature.)

but it's my opinion (of database manager)

[Mirviriam]

just wandering why you seek the number of user

What i mean is each new style of campaign is separate, on a single campaign, you can have many dm (1 table)

Not sure what you mean here?

On the data base side, I will have all campaigns in single set of tables. Then use cross/pivot tables to insert new variant rules etc. My database structure already allow's multiple DM's, domain rulers, players per regent, etc.

The only real exclusions I have are:

holding can't belong to more than one province
province can't belong to more than one domain
province can't belong to more than one region (birMail doesn't use regions, but does have continents ... there will be regions in my DB as there are in the books)

[Dyark]

province can't belong to more than one domain

What about multi regent (different domain) for one province like in Kinasi/Vosgaard ?

[Arjan]

What about multi regent (different domain) for one province like in Kinasi/Vosgaard ?


well there are also cases that domains have more then one ruler, 3 bros mages, college of sorcery, sword mage (rumor)
i.e. rule by council

i would not worry too much about the situation we talked about earlier in vosgaard.. just simple split them into two domains in the domain table..
even though the provinces "belong" to one realm.. the provinces are in fact split between two domains. (but still linking to the realm id)

the realm table is nothing more then a name of a country reference

and also try to put your thoughts into the player.. what would he do? i assume you give the player the ability of renaming their domains/provinces/realms

[Dyark]

Rename domain : Yes
Rename province : Why ? :confused:


How do you divide the taxes on multiple regent per domain (like the red kings in Kinasi) ?

I am working are on the structure of the UI, need to find a good map api, the rest will be easier as I go along !

[Arjan]

Rename domain : Yes
Rename province : Why ? :confused:


well you will always have players that want to rename things.. its their victory, and they always want to change things to show the outside world of their power and actions..
in the real world history this has happened as well.

and really, it does not matter, because they are linked on ID..

BUT if you are thinking about using the province table as non-editable for multi campaign..
yes you have a problem



How do you divide the taxes on multiple regent per domain (like the red kings in Kinasi) ?

take a look at the 3e rules for ruling a counsil
- in 3e the taxes are set to constants amounts.. no variables.
- ruling by counsil takes the avarage bloodline score to determine the max rp

[Dyark]

ok but taxes may not be higher because there are more regent !!

[Mirviriam]

"Jimmy, I'd like to take that question & field as my own..."

Taxes in the province should be the same. As per the game mechanic there's no geographical element for assigning taxes. Even if regent A controls the western half & regent B controls the eastern half of the province, there's still no method to express separate taxes in the original or BCRS version of birthright.

Is these a feature you want to add later, or did I just miss what you meant?

[Dyark]

it is because we prepare the database and there are a lot of multi regent per domain in there (Anuire as chymereaon, but afeter that you get a lot of them)
And I was wnadering how to handle that as a game mechanic so i can translate that in the web game.

[Arjan]

i think in order to handle multi regent per domain you have to select everything by domain, instead of regent.

then a simple function to calculate the avarage to determine max RP.

the RP/GB pool is in the domain table anyway

[Dyark]

so if I understand you double a domain because there is more than one regent ?

or you create a pivot table for domain and referred regent ?

[Arjan]

so if I understand you double a domain because there is more than one regent ?

or you create a pivot table for domain and referred regent ?

hmm a pivot table is not needed.. regents can thus have the same domain_id

select from regents where domain_id =

instead of select from domains where regent_id =


so the whole setup should be domain focused instead of regent focused..
(also good in cases like when a regent some how dies... or when transfering a domain to another char)

perhaps in cases when a player has multiple domains (like a covert one) the domain table should have the player or user ID
if i am right, i have the regent id in the user table, but would probably be better to have it in the domain table..
also easier when assigning it to users or play as DM

[Mirviriam]

hmm a pivot table is not needed.. regents can thus have the same domain_id

select from regents where domain_id =

instead of select from domains where regent_id =


so the whole setup should be domain focused instead of regent focused..
(also good in cases like when a regent some how dies... or when transfering a domain to another char)

perhaps in cases when a player has multiple domains (like a covert one) the domain table should have the player or user ID
if i am right, i have the regent id in the user table, but would probably be better to have it in the domain table..
also easier when assigning it to users or play as DM

Design-wise a pivot is better...I think it's 4th form?

[Mirviriam]

so if I understand you double a domain because there is more than one regent ?

or you create a pivot table for domain and referred regent ?

It has nothing to do with doubling anything...so far as I can tell. There's really two ways. First you use the pivot(cross) table:

table: regent
regent_id
fields:
name
level
stats
etc...

table: domain
fields:
domain_id
name
treasury
regency
etc...

table: x_regent_domain
fields:
regent_id
domain_id

The second way, & the point Araj was making...there's an easier way. You can instead take your old database design(this is most recent i have of yours):

table: ruler
fields:
areaofdeath
currentprovince
npc_own
pc
characterownerid <---- Here is what Araj wants to point out
etc...

See "characterownerid"? If you make that domain_id. Then you don't have an issue with multiple regents controlling a country/realm. You have to do that in the provinces, structures, armies, holdings tables too!

Why? Every ruler will have a domain_id assigned. The domain will link the ruler/regent table to the provinces and holdings (everything actually).

Like so:

Regent -> Domain <-Provinces
Holdings-> Domain <-Army
Bridges -> Domain

You create the link by replacing charownerid in the Province, Holding & Regent tables. Then when you query for rulers of a domain...

SELECT r.id, r.domain_id r.name, domain_id, domain_name
FROM ruler AS r, domain
WHERE r.domain_id = domain_id

Then you can output it how ever you want. The advantage is unlimited # of rulers for any domain. Changing the name of a domain, province, holding, bridge or army is simple since it's linked via domain_id placed in each field.

Arius pointed out that the 2nd method is a bit of a shortcut. The reasoning is that logically domain id does not belong in regent table - so you should make a cross (pivot) table to link the two tables. Honestly, in a database as small as this - I don't think there's a wrong method. I prefer the cross(pivot) method but if you check my database plan currently (here (http://www.birthright.net/brwiki/index.php/User:Mirviriam/BRCS_SQL)) , you can see I used the second method (because it was easier).

[Dyark]

Last one on this topic.

Since you put you RP & GB in the domain, it means you will use it per case of use for multiple regents ??



PS How are you guys advancing on the rest ??

[Arjan]

PS How are you guys advancing on the rest ??

to be honest.. i havnt done much lately..

[Dyark]

I discovered some code for the map api, so i working on it (also finishing my vector map)

[Mirviriam]

I've been waiting 3 months to start my project. Database model is morphing.

In the mean time I've been creating little pages to sharpen my skills.

Last weekend I did a php tool to drop in a directory & query any database to list all tables & their fields. (I actually spent 5 hours working on a spider to capture pages,...finding mac osx 10.3.9 has bad EOF marker which prevents it from working for me).

This weekend I'll do another tiny tool or improve last weekends. I might do a random realm generator.

If you guys wanted to setup read only access on your servers & exchange login's for that - I don't have anything really on mine, but I could create update scripts for fields that are wrong in database & leave them on here for you guys.

Otherwise, I'm just plodding along on my editor for the fields of database...so people without tech skills can help correct wrong data.

[Arjan]

I've been waiting 3 months to start my project. Database model is morphing.

In the mean time I've been creating little pages to sharpen my skills.

Last weekend I did a php tool to drop in a directory & query any database to list all tables & their fields. (I actually spent 5 hours working on a spider to capture pages,...finding mac osx 10.3.9 has bad EOF marker which prevents it from working for me).

This weekend I'll do another tiny tool or improve last weekends. I might do a random realm generator.

If you guys wanted to setup read only access on your servers & exchange login's for that - I don't have anything really on mine, but I could create update scripts for fields that are wrong in database & leave them on here for you guys.

Otherwise, I'm just plodding along on my editor for the fields of database...so people without tech skills can help correct wrong data.

i know flip is running subversion on this server as well, ill ask him for an account.

[Mirviriam]

Good stuff, I'll try to find a version of subversion that runs on 10.3.9 :)

[Dyark]

Anybody worked on anything except me for a couple of months ?? :confused:

[Mirviriam]

After site went down again I haven't wrote any actual code.

This was taking too long & I never went back to project after crash. I seriously consider writing more, but it was so much waiting around for responses. If I didn't wait, I might as well been writing my own inflexible version of birthright.

I think really what bugs me the most, is that I can code for the battletech community which is being used to run 3 campaigns right now or I can keep checking the forum here for replies, if & when the website is up.

[Dyark]

for my part, i have already started the coding, it is advancing well, i am looking for beta testting around march (hopefully )

I did not change my mine on flexibilty, it is really flexible and also i am commenting my codes a lot so somebody who wants to take my work can do it easily (all in actionscript 3 and php)

I am aslo building tools for customs data like (that is a big wish) every regent has to developp their provinces and provide adventure data for somebody other than him !

we will see anyway :D

[Vicente]

I have been working also in my free time in a tool to manage Birthright PbeM games, I posted this in the Empire's Twilight PbeM forum, but I'm going to repost it here, maybe someone finds it interesting.

All this work is in .NET, and the idea is to produce 2 tools:

- One is the game manager: this can be deployed as a desktop app for the GM only, or as a web app (based on Silverlight) for the GM and the players. The server side would need Windows (Professional XP/W7 or Server to host the web server that is IIS), and SQL Server as the DB (SQLS Express works).

- A data importer from Excel. This tool will allow to fill the initial game data (provinces, holdings, units,...)

I have done a lot of work on the manager, although December has been horribly slow (I have been out on holidays a lot), and the data importer is just a proof of concept (although I have all the important parts done, now it's just a matter of create the UI).

Edit: no images as it seems BR doesn't allow more than 10 in a single post, so I have posted links to them.

----

I have continued advancing a lot, although far more slowly than expected as I have had to fight some bugs with LightSwitch (it's in Beta 1 state) and I had to re-do some things several times until I got them more or less right (specially how to handle visibility of data and game rules). I hope I'll hit soon the point when I'll upload the code to an open source repository (probably Codeplex, not sure yet). But if anyone wants to check let me know and I'll send it to you (a developer version, so be ready to install a lot of Microsoft/.NET things to try it).

A little overview on how things are going (long post with lots of images).

First, uses of the tool:

1) The tool can be installed in a GM computer as a desktop application. Only the GM will be able to access it, and he will need to find some way to give information to the players (the application can export some information but it is very limited. That may change in the future as it's technically possible to write a tool that queries the DB directly and generates html, pdf, word,... but it's a pain to do).

2) The tool can be installed in a web server as a web application. This allows the GM and players to access it, to modify and check the game data. Like the website we have here in ET for example.

So, let's assume you install and start the tool. First thing you will see is that you have to log in, by default the tool will come only with an "Administrator" user created:

http://kartones.net/images_posts/master/BRManager/C1.png

With him you must create the other users of your game: game masters and players. In the "Roles" screen there are three defined roles: Administrator (with permissions to administrate users and roles), Game Master (with permission to change any game data) and Game Player (with permission to access player screens).

So far the tool supports Birthright 2e and Empire's Twilight rules (B3e coming when I learn it), so you need to add a special permission to Game Master and Game Player to let them know which rules are going to be used in the game.

http://kartones.net/images_posts/master/BRManager/C2.png

Then you just create the users and you are ready to start.

http://kartones.net/images_posts/master/BRManager/C3.png

Once users are created, people will log to the game. First, a GM needs to log to create the game data (characters, domains,...). There is an external tool that loads data from Excel to the game so you don't have to write it all every time you start a game (it's pretty rough right now, I have to improve it a lot).

So, you are a GM and you log, this is what you will see:

http://kartones.net/images_posts/master/BRManager/C4.png

GM data is divided in three parts:

- Basic Data: this is data that is independent of the ruleset you are using. For example, Terrains. No matter if in BR 2e plains can have up to 10 population and in ET is 8 (or whatever), the data "structure" is the same, the only thing that change are the values.

- Birthright 2e Data: this is data that depends on the ruleset. For example, land units. All rules have land units, but they are so different that is impossible to fit them all in the same table in a way that makes sense. So I've decided to separate that data into a table for each ruleset. This appears because we set our players to have the B2e permission, if we have set the ET permission, then we would be seen the Basic ET Data screen instead.

- Game Data: the really interesting data, the actual game. Here the GM administrates characters, domains, units,... This is the hardest part to design and code. So far, only the "Players" and "Characters" are fully done. "Domains" is nearly done, but as things advance it will have to evolve (adding units, economic overview, trade routes, diplomatic agreements,...).

There are a few special fields on all the tables.

- "Visible for Players?": data marked is visible for players (public). For example, you may create a character that is "The Gorgon", but you won't make him visible. But in general this applies to all data: you don't want players to create characters with Azrai bloodline? Don't mark that derivation as visible and they won't be able to. And so on.

http://kartones.net/images_posts/master/BRManager/C5.png

- "Player Information"/"GM Information"/"Owner Information": this text fields are so players and GMs can add random information there. For example, I'm the onwer of the Boeruine Trading Guild, I may put some text into the "Player Information" textbox (background of the guild, public known info,...) and all players will be able to read it. But text in the "Owner Information" will only be visible to me (and the GMs :p). GM Info is only visible to GMs and can be useful for them to take notes on events, problems,...

http://kartones.net/images_posts/master/BRManager/C6.png

- "Are Rules Enabled?": this field may disappear, not sure. It indicates if the application tries to enforce the rules or not. For example, only one regent can have a level 1 or higher holding in a level 2 province in B2e, so with rules enabled the game will give an error if a second one is tried to be created. If this flag is disabled, the application won't say anything. But given that BR is so exception based, and that people tend to know the game pretty well (specially GMs), I'm not sure if this will be very useful. Comments are appreciated :)

Some screens of the data that works. First Players. One important thing is that they should have the same name as the User associated with it, or the app won't find the player data.

http://kartones.net/images_posts/master/BRManager/C7.png

Game characters.

http://kartones.net/images_posts/master/BRManager/C8.png

Although it doesn't appear there (it's to the left), characters are marked as GM Approved. When it's checked, it means the GM has reviewed and approved the character. Players can create new characters for their domains, and then when the GM checks this list he will receive a warning if there are characters that aren't GM approved yet.

Last example of GM screens, the detail of Medoere (per the RoE rules with some concessions from my part).

http://kartones.net/images_posts/master/BRManager/C9.png

Now, imagine you log as a player. This is what you will see:

http://kartones.net/images_posts/master/BRManager/C10.png

Pretty similar to the GM part. Basic Data and Basic 2e Data aren't very interesting, they are like the GM sections but only visible data will be shown and the GM Info is hidden. This is to check information without needing the rulebook or going to Birthright.net and so on.

Game Data is more interesting, let's see for example the public information for Medoere.

http://kartones.net/images_posts/master/BRManager/C11.png

As you can see, the data that is shown is filtered (some things don't appear) and other things are "masked" as they are here in ET (you don't see the real values, just hints). Also I have hidden the Sources from Hermedhie.

And Own Data is the player domain, characters,... The player can modify very few data: add new characters, add turns (in the future :p), modify public information,... The idea is that the GMs are the ones changing things when they solve turn submissions.

This is for example Medoere when seen by the owner player.

http://kartones.net/images_posts/master/BRManager/C12.png

He can see the exact details of the holdings in his lands, but he can't see the hidden Sources.

And that's it so far :) The tool is becoming much bigger than I have anticipated (I suck at estimations :p), but I think it's coming along pretty well. So far my main problems are:

- Handling multiple different rules, specially economy. I bet I'll have problems also with other weird things like buildings and spell effects.
- App is super ugly. And it will remain pretty ugly in the end :( This can be fixed doing custom controls, but for that you need two things: artistic talent (I lack that) and a TON of time (I lack that too).
- App is slow. Microsoft has said they are working on this, who knows.
- Microsoft breaking the app. I'm using a Beta 1 software, it will probably change when it moves to Beta 2, and change again when it moves to RTM. I expect things to break (nothing too serious, but it could be all the app, I have seen it before :p).
- I need more free time :p

And my main work:

- Adding more tables and screens. Specially Domain Provinces, Holdings, Units, Trade Routes, Ley Lines, Vassalages, Mercenaries.
- Work on the data importer app.
- Think about a data exporter app (not sure I'll ever tackle this).

Also, thanks to Gio: even if I have not seen anything of your code in ET, it has inspired me in some places so far :) And to Gustav for sharing those huge excel spreadsheets to test data importing :)

[Dyark]

I need more free time so do I, so do I

great work by the way, it looks a lot like a custom Microsoft Access with powerfull custom GUI.

Question for Excel : have you think of using the format csv (there is a common database that the community is trying to build) in mySQL, but you can export to csv format so no double work there ?

For my part, the tools is made with flash but I use a lot of actionscript classes so any other actionscript scripter (like flex) can see and rework my work (say thta a few times :D )

I want a really small web tool so you don't need to upload the version but play directly on the web. All data is coming from dynamic php pages (you fill out what you want and the result is transform for you, no pre build SQL data.

map is vectorize, so zoom and position is really easy : you are the regent and you build a bridge, well since i am a lazy GM (in a sense, i like people working for themselves ;) ) you place the bridge in the province that you own yourself (it is only coordinate)
the build up is also for future consideration (rule changes, new continent etc)

It is a lot of work, first i want it ready for christmas 2010 but wow was I off, now I wich June 2011, but it looks more like fall 2011, we will see.

and last thing i want all none npc domain to be played by PC (if possible), that's a lot of them !

well that it for now :cool:

[Vicente]

great work by the way, it looks a lot like a custom Microsoft Access with powerfull custom GUI.


Thanks! :) I agree it's pretty similar to forms over Access :)



Question for Excel : have you think of using the format csv (there is a common database that the community is trying to build) in mySQL, but you can export to csv format so no double work there ?


Not sure I understand the question :S I won't export anything as I'm using some data files that aren't mine. I will make a tool capable of importing data from an Excel file (2007 onwards) as there's a driver that allows you to read data from there as if it was a database (so I can use the same code to get data from SQL Server, MySQL or Excel :)).

Importing data from CSV shouldn't be hard, it's a pretty simple format to parse :)



map is vectorize, so zoom and position is really easy : you are the regent and you build a bridge, well since i am a lazy GM (in a sense, i like people working for themselves ;) ) you place the bridge in the province that you own yourself (it is only coordinate)
the build up is also for future consideration (rule changes, new continent etc)


I don't think I'll tackle the map problem, I could check Bing Maps API, but I really don't like anything related to web programming :S



It is a lot of work, first i want it ready for christmas 2010 but wow was I off, now I wich June 2011, but it looks more like fall 2011, we will see.


Agreed, it's a ton of work, I was expecting to finish this in 1 month and well... :p



and last thing i want all none npc domain to be played by PC (if possible), that's a lot of them !

well that it for now :cool:

Well, the idea is that the GMs setup what is playable and what not, that's up to them. In both PbeM games I'm doing Avanil and Boeruine are playable, but they aren't in RoE, so well :)

[Mirviriam]

for my part, i have already started the coding, it is advancing well, i am looking for beta testting around march (hopefully )

I did not change my mine on flexibilty, it is really flexible and also i am commenting my codes a lot so somebody who wants to take my work can do it easily (all in actionscript 3 and php)

I am aslo building tools for customs data like (that is a big wish) every regent has to developp their provinces and provide adventure data for somebody other than him !

we will see anyway :D

I also realized our databases were extremely inflexible. That's why I brought up the splitting all tables. I think I said by birthright game stats, then a cross connected table for metagame(electronic stats, like location of character etc).

I took that a little farther in past few months. That's how I came to idea of the action system. There's a down side. You need two queries for every query in the old system. One to prep the action & load the variables (table names) to query. Then one to get the action/character specific information to use in the formula. I'll link from here to that post in a few minutes.

[Mirviriam]

so do I, so do I

great work by the way, it looks a lot like a custom Microsoft Access with powerfull custom GUI.

Question for Excel : have you think of using the format csv (there is a common database that the community is trying to build) in mySQL, but you can export to csv format so no double work there ?

For my part, the tools is made with flash but I use a lot of actionscript classes so any other actionscript scripter (like flex) can see and rework my work (say thta a few times :D )

I want a really small web tool so you don't need to upload the version but play directly on the web. All data is coming from dynamic php pages (you fill out what you want and the result is transform for you, no pre build SQL data.

map is vectorize, so zoom and position is really easy : you are the regent and you build a bridge, well since i am a lazy GM (in a sense, i like people working for themselves ;) ) you place the bridge in the province that you own yourself (it is only coordinate)
the build up is also for future consideration (rule changes, new continent etc)

It is a lot of work, first i want it ready for christmas 2010 but wow was I off, now I wich June 2011, but it looks more like fall 2011, we will see.

and last thing i want all none npc domain to be played by PC (if possible), that's a lot of them !

well that it for now :cool:


That kind of sounds like it would work as a front end to the mechanics of what I am doing.

The obvious issue is my database has all campaigns by every dm in the same table for provinces, domains, holdings, structures & Dyark you stated you were creating a table or database for each campaign you wanted to play (which was just to inflexible for me).

The second issue is like Vincente, I have two or three tables for characters, holdings, provinces, domains based on if they are actually information from the book or created to make the electronic version of the game work.

[Vicente]

I have also gone the route of one DB for each campaign. I don't see why that's inflexible (may be something I don't know of MySQL), but in SQL Server I can have 324234 databases if I want, and I don't have to mix things together and fill all the tables with CampaignId fields :S.

For example, if you have all campaigns in the same tables, and you have a guy who is GM in one and player in another, enforcing permissions at DB level is impossible (at least in SQLS). If you have each campaign in one DB, then you can give the GMs DB admin rights for that DB and they won't be able to see anything else.

[Dyark]

I also realized our databases were extremely inflexible. That's why I brought up the splitting all tables. I think I said by birthright game stats, then a cross connected table for metagame(electronic stats, like location of character etc).

I took that a little farther in past few months. That's how I came to idea of the action system. There's a down side. You need two queries for every query in the old system. One to prep the action & load the variables (table names) to query. Then one to get the action/character specific information to use in the formula. I'll link from here to that post in a few minutes.

I just read the other post and thinking about this. Loading table is really small (i just made a big query crossing 9 tables and loading a lot of data, it is only 82k so even if i load data to use in the actions, is is really small.

[Dyark]

That kind of sounds like it would work as a front end to the mechanics of what I am doing.

Exactly, I want it to be very flexible (used by the community), so I would have my campaign, but somebody else could create is own campaign easily with the same tool.



The obvious issue is my database has all campaigns by every dm in the same table for provinces, domains, holdings, structures & Dyark you stated you were creating a table or database for each campaign you wanted to play (which was just to inflexible for me).

What I meant is a datatable that handle all the change in the other tables (I am not sure it would be easy, it is something a friend build for a different game)



The second issue is like Vincente, I have two or three tables for characters, holdings, provinces, domains based on if they are actually information from the book or created to make the electronic version of the game work.

What do you mean on this :confused:

[Mirviriam]

We are building a game system on top of the rules laid out in the 2nd or 3rd Edition Birthright book right?

Well, assuming you answer yes:

There are several types of data.

The base of all the data is anything we copy out of the 2nd Edition or 3rd Edition handbook. I personally have this information in it's own table. IE: Character table has fields id, name, age, gender, race, strength ... total levels. Then there's a crosslink table for classes since that's the easiest way to allow multiple classes & levels.

Where I think we differ is that when I want to record information the Handbook's do not have listed (like location of regent etc), I create a table with the same name & put a "M_" infront of the name. IE: character table has a second table named M_character which holds x & y coordinates, if the character has trained this turn (since birthright forbids training 3 rounds in a row, this is important), etc.

It's an information handling thing. Look up metadata on the web for a better explanation. The idea is that, it's data which acts on other data.

[Dyark]

Well, assuming you answer yes:
assuming great :D




I create a table with the same name & put a "M_" infront of the name. IE: character table has a second table named M_character which holds x & y coordinates, if the character has trained this turn (since birthright forbids training 3 rounds in a row, this is important), etc.

just wandering why you use x and y when you can determine them buy le width and height of the province object ?



It's an information handling thing. Look up metadata on the web for a better explanation. The idea is that, it's data which acts on other data.

For me training goes in round_actions_all table which gives me the Turn, Round, Action number, Regent, Showdetails (what the regent wants to appear in the history book), special request (message to the GM), GM approve.
So the SQL request tells me if the PC trained in the past round and reject the action automaticaly, when the regent submit it.

QUESTION : all the classes and level of the 3e edition, do you have data on this ?? I just don't want to write them all by hand (or keyboard :p )

[Mirviriam]

The other thing to recall is with view's all your concerns or needs to use one database per campaign go away.

Also, don't forget the hidden costs of database to database projects, there's metadata table on the server & communication costs too.

[Mirviriam]

I'm doing a quick and dirty ruby on rails project to test some things for another project.

The question I had here on how to implement multiple campaign's into the same database comes up again.

I'm pondering a separate campaign cross connect table (pivot) for every table or faster method, stick the campaign_id into already existing cross connect tables.

Example: Of the 2nd method (not the names I'd use or full listing of fields)

Table #1: campaign_id, campaign_name
x_table_1_2: campaign_id, person_id, isDM? //every participating in campaign & unlimited # of dms
Table #2: person_id, person_name, username, password_hash, etc
Table #3: province_id, province_name, province_owner_domain_id, etc
x_table_3_4: province_id, domain_id, campaign_id // because if multiple campaigns we could have identical pairings of the first two ids
Table #4: domain_id, domain_name, treasury_gold, treasury_rp, etc
x_table_2_4: domain_id, person_id, campaign_id // multiple regents of domain & campaign allowance
x_table_4_5: domain_id, holding_id, campaign_id // because if multiple campaigns we could have identical pairings of the domain & holding id
Table #5: holding_id, holding_name, holding_level, etc

I like the idea of doing it fast ... but I also like the idea of better normalization (campaign_id mapping to all other table's by their id in it's own cross or pivot table).

What do you guys think?

More normalized version would be the same as above, but remove campaign_id from x_table and have a second x_table_camaign (which holds campaign_id & id of whatever type it connects too)

[Vicente]

Why not using just a single campaign per DB to avoid the extra where and column?

[Mirviriam]

Because none of the current companies (which were big enough to get noticed by me) that run web board games do, due to many database transactional costs. SQL servers can handle thousands of databases on their cluster, but a single sql database is built to hold millions of records without slowing down. Additionally, most hosting sites have database limits.

I'd like to build it so if I do enjoy the work and have time - it would be usable by people besides me later on!