DB Design Questions

[Vicente]

Topic to talk about different DB approaches to handle Birthright PbeM games, to avoid cluttering other threads.

[Vicente]

So, first two questions:

1) why putting all Birthright games in the same DB instead of using different DBs for each game? What's the advantage of it? I see some problems to that approach (an extra Id everywhere, harder/impossible to give roles at DB level,...).

2) What level of rules enforcement should do the application? DnD in general is very exception based, and more in BR where the actions are very open ended. The code can try to enforce the rules (simple rules like no holding with level higher than province, more complex rules like result of a contest,...) or the DB can be just a data repository and the GMs/Admins are the ones that will handle changing the values as needed.

[Dyark]

So, first two questions:

1) why putting all Birthright games in the same DB instead of using different DBs for each game? What's the advantage of it? I see some problems to that approach (an extra Id everywhere, harder/impossible to give roles at DB level,...).

mainly to decrease the load of server (can run two campaign on the same server with the same DM without recreating everything.



2) What level of rules enforcement should do the application?

It is really flexible, all math is process by the program, but everything can be DM approved after.


DnD in general is very exception based, and more in BR where the actions are very open ended. The code can try to enforce the rules (simple rules like no holding with level higher than province, more complex rules like result of a contest,...) or the DB can be just a data repository and the GMs/Admins are the ones that will handle changing the values as needed.

I use and action request (SQL code is transfer) in a table with the action number, owner of the action, roll, RP, contest RP, DC, turn, round and year

[Vicente]

mainly to decrease the load of server (can run two campaign on the same server with the same DM without recreating everything.

Mmm, I think there's something in MySQL that works pretty different than SQL Server. I can have 10 DBs in the same SQL Server instance, and it has no impact in the performance of the server.

About creating information, you need to have a "base" DB (or script to populate the data, or tool,...) and then just replicate the DB to generate a new game. It's true that with X DBs you have X times the same data, but the size of that repeated data (terrain, unit stats,...) is very small.

Another issue is that having everything in the same database is going to make all queries take longer, but not sure if the DB is going to get ever to the size where that's a real problem.



It is really flexible, all math is process by the program, but everything can be DM approved after.

I use and action request (SQL code is transfer) in a table with the action number, owner of the action, roll, RP, contest RP, DC, turn, round and year

Yeah, the basic costs can be handled more or less in simple cases (I have a hard time imagining a DB that can calculate automatically the cost of moving an army through the shadow world...). But I was referring more to the effects of the actions: if that should be handled by a GM or by the code, for example in something like a Rule Holding.

[Dyark]

Mmm, I think there's something in MySQL that works pretty different than SQL Server. I can have 10 DBs in the same SQL Server instance, and it has no impact in the performance of the server.

About creating information, you need to have a "base" DB (or script to populate the data, or tool,...) and then just replicate the DB to generate a new game. It's true that with X DBs you have X times the same data, but the size of that repeated data (terrain, unit stats,...) is very small.

Another issue is that having everything in the same database is going to make all queries take longer, but not sure if the DB is going to get ever to the size where that's a real problem.

I am still thinking about it, the limit is more the provider than the server (mySQL and the size of the database will have no impact on speed, it will be too small anyway, for server purpose of course, cause it is huge in data)




Yeah, the basic costs can be handled more or less in simple cases (I have a hard time imagining a DB that can calculate automatically the cost of moving an army through the shadow world...). But I was referring more to the effects of the actions: if that should be handled by a GM or by the code, for example in something like a Rule Holding.

Well the basic DC, effect, roll, RP spent, and other small thing is easy to compute.
The GUI i am building is the GM will have to say if ok or not for all actions (just one click) if not, he can rejected it or change some stuff, mainly misc, and accept it afterwards.

I am also putting warning on some action (impact stuff), those will be in the database so it can be customize.

[Mirviriam]

So, first two questions:

1) why putting all Birthright games in the same DB instead of using different DBs for each game? What's the advantage of it? I see some problems to that approach (an extra Id everywhere, harder/impossible to give roles at DB level,...).

2) What level of rules enforcement should do the application? DnD in general is very exception based, and more in BR where the actions are very open ended. The code can try to enforce the rules (simple rules like no holding with level higher than province, more complex rules like result of a contest,...) or the DB can be just a data repository and the GMs/Admins are the ones that will handle changing the values as needed.

For #1 - Technically you can do it any way you want. SQL in general has 5 arch-types & more brands/crosses. When designing, remember the systems that survive are self-contained & SIMPLE. I personally put all the templates of starting as listed from handbook in to the data base that is live for two reasons.


A) MYSQL is not going to take hits on query with less than a couple million rows unless you really try to slow it down, but having too many tables & joins etc. Just creating a sql database does not mean it's properly tuned. Like everything in technology regarding servers, the initial setup makes broad assumptions of what you will be doing. I guarantee speed wasn't the primary function when my-sql was wrote.

B) It's a live system. I will hand punch the startup stats if needed. The utility I envisioned will have a feature that asks you - is this a template? If you make yes to a campaign as template - all information entered can only be edited by the owner. Other DM's can then clone that template & edit their version.

For #2 - Dyark does a good job of explaining. I want to add, but it's a matter of scale & data entry. To solve problems you do three things:


1) Break a problem in to portions based on purpose
2) Break those purposes in to functions that do one & only one thing
3) Recognize purposes you've solved before, then use a wrapper to adapt it

I see patterns when I look at the action system. Where I don't, I think we can leave a tool that allows the DM to create his own actions.

You note that there's lost of custom information. You also stated something about size of turn orders. Both of these concerns two second problems. Change the variable length to varchar & you're done, then create a table for custom names with an id field for itself, an id field cross connecting to the pending_action table.

[Mirviriam]

PS:

If you select edit post, then go to advanced mode - there's a check box near top to delete replies.

[Vicente]

I am still thinking about it, the limit is more the provider than the server (mySQL and the size of the database will have no impact on speed, it will be too small anyway, for server purpose of course, cause it is huge in data)

Well, if it's a technical limitation of MySQL then I say nothing. I know very little of it so I thought it would behave as SQL Server.


Well the basic DC, effect, roll, RP spent, and other small thing is easy to compute.
The GUI i am building is the GM will have to say if ok or not for all actions (just one click) if not, he can rejected it or change some stuff, mainly misc, and accept it afterwards.

Mmm, I'm my short experience with PbeM things aren't ever so "easy" to compute. For example, in my turn 3.1 submission in ET I created a Trade Route, this is the DC calculation:

Seasedge - Coere -> ? = 10 - 6 (expected, Law from Boeruine) - 6 (my own Guilds) - 7 (Commerce Skill) - X (expected, Law from Brosengae) - Y (expected, Guild from BO) - Z (expected, Commerce Skill from BO).

The final DC of the action depends on 4 different players: myself, the owner of the law of Boeruine, the owner of the law in Brosengae, and the owner of the guilds of Brosengae. The program can't tell me some of those numbers because I don't know them (guild and law levels in Brosengae), nor I'm not sure if those players will support or oppose my action (I was expecting support because I talked with the player about this, but who knows).

That's why I'm not sure it's a good approach of having some code to calculate things, it's very hard :S Something similar happens with taxes and claims in BR 2e (it's somewhat easier in 3e), you just can't calculate the income of a ruler because it may depend of the random income of others, which may depend of a third regent, and so on. :S

[Vicente]

For #1 - Technically you can do it any way you want. SQL in general has 5 arch-types & more brands/crosses. When designing, remember the systems that survive are self-contained & SIMPLE.

Well, both ideas are self-contained and simple. Just that one can only host 1 game and will repeat template data across DBs, and the other can host several, at the expense of adding one Id column everywhere and losing the capabilities of auditing the DB at DB level.

I like more the first approach as I don't find the repetition a problem and because SQLS has no probs with having as many DBs as you want. I'm trying to see the merits of the second approach (apart from not repeating data) or if it was a decision taken because of how MySQL works.



For #2 - Dyark does a good job of explaining. I want to add, but it's a matter of scale & data entry. To solve problems you do three things:


1) Break a problem in to portions based on purpose
2) Break those purposes in to functions that do one & only one thing
3) Recognize purposes you've solved before, then use a wrapper to adapt it

I see patterns when I look at the action system. Where I don't, I think we can leave a tool that allows the DM to create his own actions.

You note that there's lost of custom information. You also stated something about size of turn orders. Both of these concerns two second problems. Change the variable length to varchar & you're done, then create a table for custom names with an id field for itself, an id field cross connecting to the pending_action table.

I tried to re-explain myself in this point. It's not a matter that the turn is long, it's a matter that if a player writes 25 pages, it's because he is going out of the rules a lot. If he is going out of the rules, then all the DB work is useless because it's not going to be used as in the end the GMs will have to do everything by hand.

I have played very little PbeMs, but so far I have found that usually turn submissions tend more to the "go out of the rules" than "follow the rules to the letter", so when designing I decided to embrace that idea (that may be wrong) and not try to make the system do anything automatically because the GMs will have to overrun it constantly (which is a pain). Now, I can be wrong, surely people here have played and GM'd far more than me, so their input here would be great (just to make sure the tool suits their needs :) ).

[Dyark]

Well, if it's a technical limitation of MySQL then I say nothing. I know very little of it so I thought it would behave as SQL Server.

No, mainly the number of database that your server accept (some is 5 or 10, mine is 50 before extra fee. MySQL doesn't have limitation, it is like SQL server.



Mmm, I'm my short experience with PbeM things aren't ever so "easy" to compute. For example, in my turn 3.1 submission in ET I created a Trade Route, this is the DC calculation:

Seasedge - Coere -> ? = 10 - 6 (expected, Law from Boeruine) - 6 (my own Guilds) - 7 (Commerce Skill) - X (expected, Law from Brosengae) - Y (expected, Guild from BO) - Z (expected, Commerce Skill from BO).

The final DC of the action depends on 4 different players: myself, the owner of the law of Boeruine, the owner of the law in Brosengae, and the owner of the guilds of Brosengae. The program can't tell me some of those numbers because I don't know them (guild and law levels in Brosengae), nor I'm not sure if those players will support or oppose my action (I was expecting support because I talked with the player about this, but who knows).

That's why I'm not sure it's a good approach of having some code to calculate things, it's very hard :S Something similar happens with taxes and claims in BR 2e (it's somewhat easier in 3e), you just can't calculate the income of a ruler because it may depend of the random income of others, which may depend of a third regent, and so on. :S

if i look at your example, i have a table for oppose rulers that is set by the player with buffer RP, with incrimination of 1.

So same example part 1 (opposed ):
Seasedge - Coere -> ? = 10 + w (Opposed, Law from Boeruine buffer 6 RP, incriment of 1 (ruler may change the incriment)) - 6 (my own Guilds) - 7 (Commerce Skill) + X (SQL query for the number) + Y (SQL query for the number) + Z (SQL query for the number).

So same example part 1 (opposed Boeruine ruler live):
Seasedge - Coere -> ? = 10 + w (Opposed, RP decided by ruler) - 6 (my own Guilds) - 7 (Commerce Skill) + X (SQL query for the number) + Y (SQL query for the number) + Z (SQL query for the number).

So same example part 2 (neutral):
Seasedge - Coere -> ? = 10 - 6 (my own Guilds) - 7 (Commerce Skill).

So same example part 3 (favored):
Seasedge - Coere -> ? = 10 - w (Favored, Law from Boeruine buffer 2 RP, incriment of 1 (ruler may change the incriment)) - 6 (my own Guilds) - 7 (Commerce Skill) - X (SQL query for the number) - Y (SQL query for the number) - Z (SQL query for the number).

I hope this is clear enough.

[Vicente]

No, mainly the number of database that your server accept (some is 5 or 10, mine is 50 before extra fee. MySQL doesn't have limitation, it is like SQL server.

Ah ok, it's related to the host. Much more clear now.



if i look at your example, i have a table for oppose rulers that is set by the player with buffer RP, with incrimination of 1.

So same example part 1 (opposed ):
Seasedge - Coere -> ? = 10 + w (Opposed, Law from Boeruine buffer 6 RP, incriment of 1 (ruler may change the incriment)) - 6 (my own Guilds) - 7 (Commerce Skill) + X (SQL query for the number) + Y (SQL query for the number) + Z (SQL query for the number).

So same example part 1 (opposed Boeruine ruler live):
Seasedge - Coere -> ? = 10 + w (Opposed, RP decided by ruler) - 6 (my own Guilds) - 7 (Commerce Skill) + X (SQL query for the number) + Y (SQL query for the number) + Z (SQL query for the number).

So same example part 2 (neutral):
Seasedge - Coere -> ? = 10 - 6 (my own Guilds) - 7 (Commerce Skill).

So same example part 3 (favored):
Seasedge - Coere -> ? = 10 - w (Favored, Law from Boeruine buffer 2 RP, incriment of 1 (ruler may change the incriment)) - 6 (my own Guilds) - 7 (Commerce Skill) - X (SQL query for the number) - Y (SQL query for the number) - Z (SQL query for the number).

I hope this is clear enough.

And how do you handle bidding or RP buffers? In a turn several actions may be opposed or supported, so the RP pool can be emptied and you have to go back to the GM to rule which actions will be supported more or less (or the pool is divided evenly).

Edit: also, take into account that oppose by pair of rulers is not enough. I have myself supported people only for certain actions or only for certain actions in certain places. The table could have also those columns and by default oppose everyone (or ignore, not sure which makes more sense as default) except for the things filled there (to make filling that table less horrible). The calculation query becomes a little harder, but well...

[Dyark]

And how do you handle bidding or RP buffers? In a turn several actions may be opposed or supported, so the RP pool can be emptied and you have to go back to the GM to rule which actions will be supported more or less (or the pool is divided evenly).

Edit: also, take into account that oppose by pair of rulers is not enough. I have myself supported people only for certain actions or only for certain actions in certain places. The table could have also those columns and by default oppose everyone (or ignore, not sure which makes more sense as default) except for the things filled there (to make filling that table less horrible). The calculation query becomes a little harder, but well...


The query part is easy, the main problem is indecision by player or if they decide to skip their set up or simply they do not update it and find they don't have enough RP for other actions.

The buffer RP is mainly for regent who do not want to verify all the actions they are implicated everyday.

The prefer situation is when the regents follow they actions so they can bet if they want (live or not) within 24 hours of the DM warning.