SQL layout

[Mirviriam]

I wanted to get birthright wiki on to an XML scheme several years ago but it never happened. So now, I'm posting what I have envisioned for my SQL data base on here.

http://www.birthright.net/brwiki/index.php/User:Mirviriam/BRCS_SQL

My wiki knowledge is limited...if you guys want to clean up the presentation go for it. I don't want to mess around with doing tables inside a wiki...since some of my explanations will span the entire page anyways. Maybe I could type it into a note pad or excel file & upload it if you guys needed a nice layout.

[Mirviriam]

I've begun fleshing out the DB on my MYSQL server.

Ok so I've broken it down in to two parts:

The wiki database map - for all the non-technical people. The link is at the top of this thread.

Actual MYSQL server I have the Database on right now...

username: mreski_viewer
password: 123456



Just for ground rules, please discuss anything the wiki that interests you, but leave the "In DB: & Updated" fields to me for now. Once we get going, this will change - just so I can keep things straight on my end.

[Arjan]

Mir,
are you using the database you posted on the wiki, or the sql file posted in the other thread?

I seem to have a lot of issues with the sql file, and it is soo polluted with strange things like html tags in fields that are ints, and many columns that appear in all tables.
plus the naming is sometimes confusing (sometimes camelcase, lowercase, or even connected with underscores while other tables are not), or even tables are missing (domains, lieuts etc)
and some data are not in the appropriate tabel

so i am gonna have to redo the whole database to clean up the mess and create a more understandable structure folowing the BR naming convention.

countries -> realms
rulers -> regents
relations -> alliances
<does not exist> ->domain (contains landed,guild,law,source,temple domain info like income RP and GB, etc etc.. in the sql file this is tied to the "ruler" .. imo you dont want that, since rulers can die etc..

[Dyark]

tell use exactly what you want to change and we can do it (a few minutes - hours) for me !

[Arjan]

its not what i want to change, but merely asking if there is a more structured one, because the SQL you and me posted are based on a dump from birmail enhanced with own stuff, but not in a logical way.

What i was just wondering if you guys already use a more structured version similar like the one Mir posted on the wiki. (still have some questions about it though)

i can do the mysql myself, but its better to use the same DB for all tools right? and why do things double if they are already available?

the things i am missing from the SQL file are for example "views"/reference tables, no standard naming convention which make ik hard to look for matching columns in statements.
but we all have different views on naming convention, for example, i like everything in lowercase, id columns with the table name before it (regent_id) and then in other tables for example prov_regentid, and not something like cCharOwner.
even worse, i like all columns to have the table name hehe regent_name, regent_description etc (but even i am not consistant, because the table is called regents)

i am having somehow lots of problems with join statements complaining the column name already exists, so for all columns loaded i need to make an alias (might be a myisam thing, going to test with innodb today) just making me crazy :-)

the SQL file is also in MyIsam (easily changed to innoDB thought with dump, find and replace and reload)
but its all little things that makes it a lot of work.

i am working on a tool that is going to be heavily database active, so i am going to need foreign keys as well to protect the data from corrupting, but only want to hook them up when the DB is really finished

[Arjan]

also make more use of many-to-many (pivot) tables.

in the sql files for example we have for regents/rulers

Class1
CLvl1
Class2
CLvl2
Class3
CLvl3


why not simply create a pivot table called regent_classes as a "bridge" between regent and classes?

it would look like:

regent_classes_regentid
regent_classes_classid
regent_classes_classlevel

that way a regent with only 1 class has just 1 entry, one with 3 classes has 3 entries.. and even if he want more classes its no problem

then you can remove the columns:
Class1
CLvl1
Class2
CLvl2
Class3
CLvl3
DualClass
LvL
which in most cases only consists of values "0"

this would also go for domains.
a regent does not HAVE to be restricted to own 1 domain. in some cases (in the many pbems i played) many regents had multiple domains.
for example on time i was playing tuarhievel, and we wanted to expand our guilds to human neighbour realms, but since they "HATED" elves we created a new domain with a pupet vasal (cariele) as being the owner.. gave him a couple of GB and voila

[Mirviriam]

its not what i want to change, but merely asking if there is a more structured one, because the SQL you and me posted are based on a dump from birmail enhanced with own stuff, but not in a logical way.

What i was just wondering if you guys already use a more structured version similar like the one Mir posted on the wiki. (still have some questions about it though)

i can do the mysql myself, but its better to use the same DB for all tools right? and why do things double if they are already available?

the things i am missing from the SQL file are for example "views"/reference tables, no standard naming convention which make ik hard to look for matching columns in statements.
but we all have different views on naming convention, for example, i like everything in lowercase, id columns with the table name before it (regent_id) and then in other tables for example prov_regentid, and not something like cCharOwner.
even worse, i like all columns to have the table name hehe regent_name, regent_description etc (but even i am not consistant, because the table is called regents)

i am having somehow lots of problems with join statements complaining the column name already exists, so for all columns loaded i need to make an alias (might be a myisam thing, going to test with innodb today) just making me crazy :-)

the SQL file is also in MyIsam (easily changed to innoDB thought with dump, find and replace and reload)
but its all little things that makes it a lot of work.

i am working on a tool that is going to be heavily database active, so i am going to need foreign keys as well to protect the data from corrupting, but only want to hook them up when the DB is really finished

That's one of the things that had me giving up hope - was how weird the birMail import looked.

I was following a normalization scheme, but I didn't really have a table/field name scheme beyond singular was the reference section or nouns. While purals or putting an "s" at the end signified it was a link table between the nouns & what I called the template tables (tables which were the same when the game starts for every senario, like classes, skills, spells, initial character stats).

Right now, I was just planning on doing whatever Dyark was doing...I'd be happy to see uniformed table names, scheme, columns etc - maybe even we setup rules for aliases so the query's all look the same too?

[Mirviriam]

Mir,
are you using the database you posted on the wiki, or the sql file posted in the other thread?

...

countries -> realms
rulers -> regents
relations -> alliances
<does not exist> ->domain (contains landed,guild,law,source,temple domain info like income RP and GB, etc etc.. in the sql file this is tied to the "ruler" .. imo you dont want that, since rulers can die etc..

Short answer is both. Long answer is if getting Dyark set on his first working campaign & then keep improving (if he allows/wants too) or branching off after would be the next bet for me.

Agreed Arjan - that's why in the table i linked to in SQL on wiki was "top down" as in the domain ID links it all, then domain table has it's properties & the provinces & holdings have the domainID in them. Allowing easy passing of control (though it happens so rarely, i doubt it will be a huge impact).

As to the pivot tables, I'm not familiar with the term - I think you mean a table that links two other tables together or a temporary table that is created simply to make a query faster? I didn't do too many of those, as I figured at first the DB would be less than 5 million entries if just in Gorgon Alliance scope...they definitely have their place though! I realized with using just "top down" & domainID as the glue that you might get 5 joins just to get the domain name, regent name, province name, holding names to show one province. I know we'll need more tables to ease the load generated from mass joins, just a matter of something I consciously ignored for the moment.

[Mirviriam]

also make more use of many-to-many (pivot) tables.

in the sql files for example we have for regents/rulers

Class1
CLvl1
Class2
CLvl2
Class3
CLvl3


why not simply create a pivot table called regent_classes as a "bridge" between regent and classes?

it would look like:

regent_classes_regentid
regent_classes_classid
regent_classes_classlevel

that way a regent with only 1 class has just 1 entry, one with 3 classes has 3 entries.. and even if he want more classes its no problem

then you can remove the columns:
Class1
CLvl1
Class2
CLvl2
Class3
CLvl3
DualClass
LvL
which in most cases only consists of values "0"

this would also go for domains.
a regent does not HAVE to be restricted to own 1 domain. in some cases (in the many pbems i played) many regents had multiple domains.
for example on time i was playing tuarhievel, and we wanted to expand our guilds to human neighbour realms, but since they "HATED" elves we created a new domain with a pupet vasal (cariele) as being the owner.. gave him a couple of GB and voila

Yea - this is in the SQL data base on the wiki already - I actually even have things in place for individual RP pools incase DM's wanted to make regents physically be present to transfer regency (even tribute RP if wanted though not by default).

[Arjan]

As to the pivot tables, I'm not familiar with the term - I think you mean a table that links two other tables together or a temporary table that is created simply to make a query faster? I didn't do too many of those, as I figured at first the DB would be less than 5 million entries if just in Gorgon Alliance scope...they definitely have their place though! I realized with using just "top down" & domainID as the glue that you might get 5 joins just to get the domain name, regent name, province name, holding names to show one province. I know we'll need more tables to ease the load generated from mass joins, just a matter of something I consciously ignored for the moment.

pivot table are quite easy, in fact we already have them, troops and sea_army. they reference a link between the rulers and the army type.
this just gives you more flexibility on "how many" armies a regent can have. he could have 1 army unit, but can also have 200.

the same i meant with classes, birmail was based on second ed, rarely a char would have more then 2 classes.
but in 3e, especially in BR many chars have 3 (scion lvl, noble lvl, and some other classes)

for example, Caliedhe Dosiere, i think he should have scion and noble levels as well. he was raised from the time he was born to become the chamberlain. with his fighter and wizard that makes him having 4 classes.
and what if he wants to take a prestige class when he lvls up? he will have 5..

so with the many-to-many (pivot) table you just add another row.

another thing is, a player might start out with his 1st class as fighter, takes two lvls and then takes another class and takes 4 lvls in that class...
with the current structure, the lvl fighter will always be his primary class, while the second class he took exceeds his first taken class.

with a pivot table its just sorting at class lvl.

its a bit more work at the beginning, but at the end you have more flexibility.. altering tables in working software is just a big pain in the ass... and is considdered a complete software upgrade (huge impact)

[Dyark]

in the sql files for example we have for regents/rulers

Class1
CLvl1
Class2
CLvl2
Class3
CLvl3


why not simply create a pivot table called regent_classes as a "bridge" between regent and classes?

it would look like:

regent_classes_regentid
regent_classes_classid
regent_classes_classlevel

Looking at what you say, it would be better that I change the database right now, then waiting to change it at the end !

Also I am a home made programmer (no studies) so there are this I do not think about when I program or elaborate things, help is always welcome.

I am not a huge fan of really long columns names, but if you think it is neceessary i wil change them.

[Arjan]

well i am a homemade programmer myself as well, but learned a lot during the years.. even a few days ago a friend told me about fereignkey relations between tables.. something i never had to use before.. but for a tool or the flash game i am working on it is going to be very needed to prevent data from becoming corrupted.

i dont really like the long names either, but i do it because i am not such a good database manager.
if you want to make a sql statement joining many tables it can get very confusing because many of the tables have: ID, Name, Description. so therefor i like the names containing the table name.. is just a way for me to make things clear and to debug through a statement to see where things go wrong.

[Arjan]

but we can come up with a naming convention that suits us all.

for example we can say:
ID fields have the table name: regent_id (while i call the table regents, plural, i like the id field to be singular.. a row contains a regent)

the other common columns i would like to see an identifier as well.
ie
Name -> regent_name , domain_name, province_name etc
Description -> regent_description, domain_description, province_description etc

i have to look over the DB to see if there are more, dont recall right now

for the other unique column name i dont really care what name is used.

[Dyark]

what do you use for the reference of the other table ?

me, personal like, is _ref so in the domain table the regent column would be regent_ref

I am also building a flash application (for me it is the easiest way) but the build has to be made by php first (all the actions are call via URLRequest statment)

[Arjan]

what do you use for the reference of the other table ?

me, personal like, is _ref so in the domain table the regent column would be regent_ref

I am also building a flash application (for me it is the easiest way) but the build has to be made by php first (all the actions are call via URLRequest statment)

i just use regentid
so currently i have
realm_regentid
prov_regentid
domain_regentid
holding_regentid
and so on...

but i am thinking of just going back to plain "regentid" for all tables.

hmm flash, curl...ill take that topic to a new thread.. curious about a few things...

[Dyark]

hmm flash, curl...ill take that topic to a new thread.. curious about a few things...


send me a link (I hate looking for it :) )

[Sorontar]

send me a link (I hate looking for it :) )

http://www.birthright.net/forums/flashtool-development-t5315.html?t=5315

[Dyark]

i have started to correct the old birdmail database, there is a lot to do, but i take off all the old non essentiel column and correct things like the mutiple class !

[Dyark]

Arjan I got a question.

if you want to have name of the column with the name of the table, this could be really long to program (long character to type).

Is this what you really want before I proceed ? Do you prefer to have alias on your request ?

let me know

[Arjan]

i have started to correct the old birdmail database, there is a lot to do, but i take off all the old non essentiel column and correct things like the mutiple class !
oh yes... thats what i was talking about :)


Arjan I got a question.

if you want to have name of the column with the name of the table, this could be really long to program (long character to type).

Is this what you really want before I proceed ? Do you prefer to have alias on your request ?

let me know

i already fixed some tables, i will post my sql file in a second

[Dyark]

waiting .... :D

PS do you know what the weighting in the table was for ??

[Arjan]

ok, had to rename the file to zip because of size limit of text files.. so just rename it back to sql and you are fine.

this is a whole dump of my database, still need a lot of fixing to be done.

but i have made some drastic changes to the DB.

added tables:
gender
terrains
lieutenants (i have my reason to not have them in the same table as regents)
titles

created pivot tables:
regent_classes
regent_titles (yes some have more then one)
lieutenant_classes


in the SQL file you will also find:


-- ----------------------------
-- View structure for regentinfo
-- ----------------------------
DROP VIEW IF EXISTS `regentinfo`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `regentinfo` AS select `regents`.`regent_id` AS `regent_id`,`regents`.`regent_abbr` AS `regent_abbr`,`regents`.`regent_name` AS `regent_name`,`regents`.`regent_description` AS `regent_description`,`regents`.`domainid` AS `domainid`,`regents`.`bloodscore` AS `bloodscore`,`regents`.`treasury` AS `treasury`,`regents`.`regency_points` AS `regency_points`,`regents`.`regency_defense` AS `regency_defense`,`domains`.`domain_name` AS `domain_name`,`genders`.`gender_name` AS `gender_name`,`races`.`race_name` AS `race_name`,`religions`.`religion_name` AS `religion_name`,`alignments`.`alignment_name` AS `alignment_name`,`bloodline_derivation`.`bloodline _derivation_name` AS `bloodline_derivation_name`,`bloodline_strength`.` bloodline_strength_name` AS `bloodline_strength_name` from (((((((`regents` left join `domains` on((`regents`.`domainid` = `domains`.`domain_id`))) left join `genders` on((`regents`.`gender` = `genders`.`gender_id`))) left join `races` on((`regents`.`race` = `races`.`race_id`))) left join `religions` on((`regents`.`religion` = `religions`.`religion_id`))) left join `alignments` on((`regents`.`alignment` = `alignments`.`alignment_id`))) left join `bloodline_derivation` on((`regents`.`derivation` = `bloodline_derivation`.`bloodline_derivation_id`)) ) left join `bloodline_strength` on((`regents`.`bloodstrength` = `bloodline_strength`.`bloodline_strength_id`))) having ('' = '');



this is the view i created to collect the regentinformation..
its not a real table, but a collection of joins!
BUT what i discovered.. you can treat it the SAME way as a table, so SELECT, UPDATE DELETE.... but be very carefull with that... i just use SELECT.. and update the corresponding table when needed.

THINGS TO DO:
well A LOT!! i noticed flash is having dificulties with the LONGTEXT fields used for description.. but i noticed that MEDIUM text works fine.. and is sufficient for more then all the data entered.
so, changing all LONGTEXT properties to MEDIUMTEXT.
really looking at other fields, and see if their type is really really needed.
in most cases a VARCHAR (20 or 30) is more then enough for most [tablle]_names (except domains.. have to put them to 40)

lots of columns need to be put back to lowercase.

another big issue is that the DATA is based on second edition, so every single information needs to be checked on the WIKI and perhaps even change some tables, so it can be used with 3e rules.

but little things like, in the domain table we have castles.. with the first domain i checked (roesone) ... they already have 2 castles:
Blacktower Castle (Province Level 3, Caercas); Castle Bellamie (Province Level 2, Bellam)
so how we going to handle that? new pivot-table for fortifications?

for the regents table i verified the first 10 regents.. 80 to go (for anuire only!!!!)
all the info should be around the wiki somewhere, but is sometimes hard to find (character stats of small domain are sometimes only listed on the realm page under important figures.

the regent_classes table only contains the first 10 regents and their classes..
i added the noble and scion class to the classes, so they can be used as well.

pffff and much much more

but the first 10 regents and their domains should be finished.

LFM dataentry people :)

[Arjan]

waiting .... :D

PS do you know what the weighting in the table was for ??

according MYSQL see below... but i never ever used it


#

WEIGHT: A positive numeric value used for comparisons.

Weights come from collating tables and go to weight strings. Often weight appears as a 4-digit number in collating tables. For example (from allkeys.txt):

0062 ; [.0FE6.0020.0002.0062] # LATIN SMALL LETTER B

This is the entry for collating element 0062, and there are 4 weights: 0FE6 and 0020 and 0002 and 0062.
#

WEIGHT STRING: A binary string, sometimes called a “sortkey” or an “ordering key”, produced by taking a series of weights from a collating table for a certain number of levels, ordering them by position and level, and outputting.

For example: starting with a character string ABC, and knowing that the number of levels is 2, look up the collating elements for A and B and C in allkeys.txt 5.0.0:

0041 ; [.0FD0.0020.0008.0041] # LATIN CAPITAL LETTER A
0042 ; [.0FE6.0020.0008.0042] # LATIN CAPITAL LETTER B
0043 ; [.0FFE.0020.0008.0043] # LATIN CAPITAL LETTER C

Result: 0FD0.0FE6.0FFE.0020.0020.0020. MySQL's weight_string() function produces a weight string.
#

WEIGHTING ELEMENT: A sequence of weights, in ascending order by level.

For example, from allkeys.txt 5.0.0:

00DF ; [.11AF.0020.0004.00DF][.0000.0199.0004.00DF][.11AF.0020.001F.00DF] # LATIN SMALL LETTER SHARP S

There are three weighting elements in this example, each is surrounded by square brackets:

[.11AF.0020.0004.00DF]
[.0000.0199.0004.00DF]
[.11AF.0020.001F.00DF]

Often one collating element has only one weighting element (which has many weights), but SHARP S is an example of expansion.

[Dyark]

but little things like, in the domain table we have castles.. with the first domain i checked (roesone) ... they already have 2 castles:
Blacktower Castle (Province Level 3, Caercas); Castle Bellamie (Province Level 2, Bellam)
so how we going to handle that? new pivot-table for fortifications?

why separate that from the province or holding, you cannot have more than 1 castle per province or more than one fortification per holding ???

[Dyark]

for the regents table i verified the first 10 regents.. 80 to go (for anuire only!!!!)
all the info should be around the wiki somewhere, but is sometimes hard to find (character stats of small domain are sometimes only listed on the realm page under important figures.

the regent_classes table only contains the first 10 regents and their classes..
i added the noble and scion class to the classes, so they can be used as well.

we must think of future use, i think regent of 2e, 3e or if somebody wants to go there 4e so i would have different table for them (doesn't increase the size of the database that much)

[Dyark]

can you put your file as a sql file ?

[Arjan]

we must think of future use, i think regent of 2e, 3e or if somebody wants to go there 4e so i would have different table for them (doesn't increase the size of the database that much)

yes, you would only have to update some of the data.. but the structure should be ok and fexible enough so you can run the version you want without changing it. (btw, i left bloodscores as they were in 2e, not an extra stat)

[Arjan]

can you put your file as a sql file ?

just rename it to sql...

[Dyark]

what time is it at your home ??

[Arjan]

what time is it at your home ??
it is now 22:45

[Dyark]

the code create algorithm create an error and stop there, can you export it without this parameter ?

[Arjan]

the code create algorithm create an error and stop there, can you export it without this parameter ?

ok weird...

just delete row 555 to 559 from the file

what tool are you using for the import?

[Dyark]

phpadmin on my server

[Dyark]

PS for gender i would have done a tinyINT for 1 2 3 so there would be one less join to make

[Dyark]

in holding you have 1081 holding and I have 1768, did you verify all yours ??

How do you handle the Anuire or Kinasi or Voosgard, becht ...

do you want to add the max source level on the terrain table ?

[Arjan]

ok after you load the file, then manually go to QUERY

and copy paste the code below



-- ----------------------------
-- View structure for regentinfo
-- ----------------------------
DROP VIEW IF EXISTS `regentinfo`;
CREATE VIEW `regentinfo` AS select `regents`.`regent_id` AS `regent_id`,`regents`.`regent_abbr` AS `regent_abbr`,`regents`.`regent_name` AS `regent_name`,`regents`.`regent_description` AS `regent_description`,`regents`.`domainid` AS `domainid`,`regents`.`bloodscore` AS `bloodscore`,`regents`.`treasury` AS `treasury`,`regents`.`regency_points` AS `regency_points`,`regents`.`regency_defense` AS `regency_defense`,`domains`.`domain_name` AS `domain_name`,`genders`.`gender_name` AS `gender_name`,`races`.`race_name` AS `race_name`,`religions`.`religion_name` AS `religion_name`,`alignments`.`alignment_name` AS `alignment_name`,`bloodline_derivation`.`bloodline _derivation_name` AS `bloodline_derivation_name`,`bloodline_strength`.` bloodline_strength_name` AS `bloodline_strength_name` from (((((((`regents` left join `domains` on((`regents`.`domainid` = `domains`.`domain_id`))) left join `genders` on((`regents`.`gender` = `genders`.`gender_id`))) left join `races` on((`regents`.`race` = `races`.`race_id`))) left join `religions` on((`regents`.`religion` = `religions`.`religion_id`))) left join `alignments` on((`regents`.`alignment` = `alignments`.`alignment_id`))) left join `bloodline_derivation` on((`regents`.`derivation` = `bloodline_derivation`.`bloodline_derivation_id`)) ) left join `bloodline_strength` on((`regents`.`bloodstrength` = `bloodline_strength`.`bloodline_strength_id`))) having ('' = '');



i added sql extension to the allowed files to upload, so below is the fixed sql file

[Arjan]

in holding you have 1081 holding and I have 1768, did you verify all yours ??

How do you handle the Anuire or Kinasi or Voosgard, becht ...

nope didnt check them.. think i only have anuire in it.

first i focus on anuire... later i will add the other regions to have a full scale play.. database should be the same

[Arjan]

blegh, i just see some of the tables are still on MyIsam..

changed them to innoDB in the file..

new file below

[Dyark]

as soon as the naming is change in a table, you want me to post it ??

[Dyark]

how do you handle a regent who also rule with a secret organization (country and organization with holding) ?

PS we can talk be email also if you want, it may be simpler

info@deismaar.com ps i am 6 hours earlier than you

[Arjan]

sure, but some of the tables need to be removed, like: Character_data and Lieutenants_data.

and other tables need to be reviewed.

perhaps we can make a thread per table with the fields in it, and discuss IF we need certain field, or if we perhaps need new ones (and for what reason)

[Dyark]

send me an email so i have your address.

do you use outlook ??

[Arjan]

how do you handle a regent who also rule with a secret organization (country and organization with holding) ?

PS we can talk be email also if you want, it may be simpler

info@deismaar.com ps i am 6 hours earlier than you

well as stated in the books is that all regents know about all the existing holdings.

and that is why i wanted to have lieutenants seperated from regents.
so in case of when a Regent wants to create a second domain (and even want to make it a secret one) then just assign a puppet lieutenant as its ruler.

but perhaps we need a extra column in the domain table.. thats why we should discuss table per table :)

[Arjan]

send me an email so i have your address.

do you use outlook ??

gmail i use

but i prefer to have these discussions here on BRnet, so the other guys can chip in when they want or have an opinion

[Dyark]

perfect i will keep it here

[Dyark]

i made a page in which i can show the progression of the database and answer questions also from the forum.

http://www.deismaar.com/correctiondatabase/index.php

do Orog receive stat bonus ?

do the class still have free action ?

i foresee a problem with your domains table, if you have an organization that becomes a ruler of provinces, how will you handle that ?

[Arjan]

i foresee a problem with your domains table, if you have an organization that becomes a ruler of provinces, how will you handle that ?

why is that a problem? the provinces table has a regent id... and a realmid.
so when a regent gets his first province and wants to start a new realm, he will get a new row in the realm table and update the regentid from the province

[Dyark]

ok, i wasn't handling it that way since i was getting the none province domain from the holding table.

[Dyark]

Treasury and regency are in both domain and regent table, which one do you use ??

the Prov_coords from province are for what kind of application ??

do you want to include the prestige classes ??

do you want to develop a character builder also ??

[Arjan]

the provinces table in my sql is limited, not all aspects are there.

i think the one in your sql file is more complete, and the fields look fine to me

only fields i know i wouldnt use are:
InGame
DeleteThis
GBIn (in domain table)
GBOut (in domain table)
RPIn (in domain table)
RPOut (in domain table)
Facade (whats this for?)
CentreX and CentreY (i am going to replace all drawing data by FXG files)
PillagedLevels (do we need this?)
LevyLevels (do we need this?)
MapArea
TurnOfLastBattle (whats this for?)
RealProvince (whats this for?)
OccupierLastTurn (do we need this?)


and ofc change:
OccupyingCharacter - occupying_regent
CharOwnerID - regentid

about char dev, i am not thinking about it now, but is the reason why i made the class system flexible by creating the many2many table regent_classes

[Dyark]

in the regents table do you want to include all the data for skills & stats also xp hp, max hp, initiative ??

I will convert my table so it can fit yours.

Also i did adjust the domain to include the organization, it is really complete, i think i have all of them.

[Arjan]

in the regents table do you want to include all the data for skills & stats also xp hp, max hp, initiative ??
if the info is there you can keep it, perhaps i will do something with it when giving out xp for quests



I will convert my table so it can fit yours.

:)



Also i did adjust the domain to include the organization, it is really complete, i think i have all of them.
nice!

did you include all regions or just anuire?

[Dyark]

did you include all regions or just anuire?

I let you guess .... :p

all of them including vosgaard, brecht, kinasi and rjujik

same thing with the holdings and owner of the holdings, i have all of them for the whole continent

[Mirviriam]

Thanks for keeping it on the forum....I am here - just trying to figure out where I fit in your programs!

If you want, then I can strip the HTML tags out of the database using a script & post it to be imported?

I'll also run some trim on it too before putting it through ...

Ari, I'm kind of hung up on the normalization thing - it makes it easier in the long run was your own line :)

I like the views too, how extensively do you want to use them? Which sort of places?

I was going to throw in a lieutenant table & just make it hold a regentID & all the information that doesn't belong in the regent table will be held there?

PS: I will do a wiki version of the database - but I'm really confused who's database is being used still ... are you both developing or moving towards a single database? I need to know what I'll be editing!

[Mirviriam]

the provinces table in my sql is limited, not all aspects are there.

i think the one in your sql file is more complete, and the fields look fine to me

only fields i know i wouldnt use are:
InGame <--- Is this the equivalent of DM allows to be used?
DeleteThis <--- KEEP! It lets us choose when to process turns & clean up
GBIn (in domain table) <--- This one & next 3 might reduce amount of php work to process turns? Less calculations during the turn process?
GBOut (in domain table)
RPIn (in domain table)
RPOut (in domain table)
Facade (whats this for?) <--- Maybe use as hidden in plain sight holdings?
LevyLevels (do we need this?) <--- If all levies aren't called useful
TurnOfLastBattle (whats this for?) <--- Allows limits on necro's raise dead
RealProvince (whats this for?) <--- Phantom spells to occupy armies
OccupierLastTurn (do we need this?) <--- If we split gold collection between occupiers during sieges this is useful in the advanced siege rules


I looked at 4 ways of running turns with variant rules ... I put my comments in there for the ones I liked!

[Arjan]

I like the views too, how extensively do you want to use them? Which sort of places?

quite a lot actually, i will be making more views when the database is finished.
to make use of views saves me a lot of complicated sql statement scripting in php.. and can easily get all the info i need with a single select * from view (where id or regentid =)



I was going to throw in a lieutenant table & just make it hold a regentID & all the information that doesn't belong in the regent table will be held there?

yes



PS: I will do a wiki version of the database - but I'm really confused who's database is being used still ... are you both developing or moving towards a single database? I need to know what I'll be editing!
yes, my goal is to lean towards a single database.
at the moment i am not editing things, and wait till dyark post something so we can review what needs to be done

[Arjan]

InGame <--- Is this the equivalent of DM allows to be used?

hmm for that is think a isPC/isNPC in the regent table would suffice



DeleteThis <--- KEEP! It lets us choose when to process turns & clean up

in a province table??



GBIn (in domain table) <--- This one & next 3 might reduce amount of php work to process turns? Less calculations during the turn process?
that is why they should be in the DOMAIN table, and not in the 1000+ provinces ;)



Facade (whats this for?) <--- Maybe use as hidden in plain sight holdings?

i dont know.. nor will i use it



LevyLevels (do we need this?) <--- If all levies aren't called useful
TurnOfLastBattle (whats this for?) <--- Allows limits on necro's raise dead
RealProvince (whats this for?) <--- Phantom spells to occupy armies
OccupierLastTurn (do we need this?) <--- If we split gold collection between occupiers during sieges this is useful in the advanced siege rules

ok, i am looking at 3e rules only.. no variant rules yet..

Although i am actually considering Gary's Conquest & War rules.. simple, d20.. and not a lot of code to script

[Dyark]

you forgot to answer me on were you want to put the GB and RP, Regent or the domain ??

I will post the tables as soon as i finish them so it will be easier that way, we can advance faster.

I would love to use one database, but i want DM to choose what option they want for their campaign so i am keeping stuff you don't want and i am making a GUI to choose the option the person want.


Is there any law claim in 3e rule ? (didn't check)

[Dyark]

domain_id
domain_name
domain_description
regentid
treasury
regency
regency_def ---- > what is this for
gb_in
gb_out
regency_in
regency_out
courtlocation --- > is this the province
courtlevel
courttype --- > what is this for
palacelocation -- > is this the province
palacelevel
attitude --- > isn't per province instead of domain ?

[Dyark]

by the way if we work together and you do not want to pass the next 5 years doing this, we better use on universal system system, what I mean is I am correcting the old birdmail database (a lot of info is already here) so if you create a different table like leutenant, keep the id intact in a column so i can erase them really fast without having to look at them one by one ok ?

[Arjan]

you forgot to answer me on were you want to put the GB and RP, Regent or the domain ??
i prefer domain




Is there any law claim in 3e rule ? (didn't check)
was about to check that as well...
..i dont think so.. but ok to keep if you like



regency_def ---- > what is this for

a pool with RP for defending against agitate, investiture etc
it helps to put a limit on how many RP you want to use against actions, so it will not empty your regency pool, or even lower your Bloodlinescore.


courtlocation --- > is this the province

yep


courttype --- > what is this for

i think it might be something like: Feudal Monarchy, rule by council or so (government type), not sure.. i will not use it



palacelocation -- > is this the province

yep


palacelevel
attitude --- > isn't per province instead of domain ?

in 3e its per domain, but i would like to use it on province level as well... province has gone in rebellion etc

[Arjan]

by the way if we work together and you do not want to pass the next 5 years doing this, we better use on universal system system, what I mean is I am correcting the old birdmail database (a lot of info is already here) so if you create a different table like leutenant, keep the id intact in a column so i can erase them really fast without having to look at them one by one ok ?

exactly, that is the purpose of scalability :)

[Dyark]

in the domain of rovninodensk (voosgard, great steppe) you have 2 regents (the tsarevo & Danica Vorac) with different treasury, how do you handle that ?

[Arjan]

according the book andrei tcherninsky took over rovninodensk and danica fled to the northern provinces.

danica rules
Dzhamhou and Pervezniki

andrei only rules
Ziass, tobol, loden, donorssk, almeysk and cheyatev

uncontrolled are:
glaznyy
pavstrakhan
ufhevsk
urgtau

so it shouldnt be a problem since the provinces point to the same realm.
since provinces are selected by regentID, either one of the rulers will see his/her provinces... which are listed with the same realm name.. (the realm name is just for naming purpose and listing)

but both should get an own domain entry (but can have the same domain name... just different domain id)

what concerns me more are the 4 uncontrolled ones... they do not have a regentId....

I have almost no knowledge on Vosgaard, are there more of these examples?

[Dyark]

Voosgard is mostly rule by many regent, the way you do it is treasury and RP are per domain and normally you never repeat the domain name (my way of thinking) in a table

for me i put the rp/gb in the regent table (since they are not transferable if a new regent comes along normally)

An other exemple is lutkhovsky, their is no regent that rules their, it is only a group of surviving provinces.

What i am doing right now is filling the treasury, RP and description of all the domains, but when I get in the no clear regent part, the treasury and rp are note clear cut, so that is why i asked you. My table will be different because of compatible of under regent (revolutionary rulers).

[Mirviriam]

according the book andrei tcherninsky took over rovninodensk and danica fled to the northern provinces.

danica rules
Dzhamhou and Pervezniki

andrei only rules
Ziass, tobol, loden, donorssk, almeysk and cheyatev

uncontrolled are:
glaznyy
pavstrakhan
ufhevsk
urgtau

so it shouldnt be a problem since the provinces point to the same realm.
since provinces are selected by regentID, either one of the rulers will see his/her provinces... which are listed with the same realm name.. (the realm name is just for naming purpose and listing)

but both should get an own domain entry (but can have the same domain name... just different domain id)

what concerns me more are the 4 uncontrolled ones... they do not have a regentId....

I have almost no knowledge on Vosgaard, are there more of these examples?

This is not a problem, use my layout for provinces belong to domain ID's ... takes care of realms without rulers and realms with many rulers, since we just put domainID in character table & province tables!

EDIT: Having two regents with same domainID in their regent table should not be an issue & write the script to pull all domain's instead of all regents & then the provinces.

[Mirviriam]

I updated my database on the wiki to be compliant with:

For the following things:

1) beginning of new fields are never capitalized
2) any 2nd word following another is capitalized
3) _ID format
4) <tableName>_name, _level, _type, _description, _shortdescription, etc

http://www.birthright.net/brwiki/index.php/User:Mirviriam/BRCS_SQL_Notation

I was reviewing my design and found a flaw in all the databases too...

When we setup the database for people, persons, regents, lieutenants, npc's etc it needs to be basic -> blooded -> lieutenants -> regents

Practically speaking:

I) All D&D stats go in first table

II) All stats related to blooded characters who don't rule + table I's character_ID in second table.

III) All lieutenant stats + table II's ID go in 3rd table type. (table I's ID is used if the lieutenant is not blooded) This arrangement allows the ID's to chain back to table I.

IV) All regent related stats no encompassed in first tables inherits in to the last table. Personal treasury & personal regency not handled in domain table come to mind immediately.

[Arius Vistoon]

first, i want to say, "sorry, excuse me", because i don't read all pages of this topic (only this page).


This is not a problem, use my layout for provinces belong to domain ID's ... takes care of realms without rulers and realms with many rulers, since we just put domainID in character table & province tables!
EDIT: Having two regents with same domainID in their regent table should not be an issue & write the script to pull all domain's instead of all regents & then the provinces.

according to me, there are a problem with your structure : domainId can't be in character table. For me, it's a non-sens (except if it's for a perfomance issue, but your database must run before you know that or your have a good experience in database management). So may be, it's your thought, you need performance ?

My opinion, it's that the domainId must in a relation table (if there are more than one regent for a domain) or a regentid in the domain table (if there are only one regent), it's your choice but it's depend of what functionnality you want ? many regent for a domain or only one

[Mirviriam]

first, i want to say, "sorry, excuse me", because i don't read all pages of this topic (only this page).

according to me, there are a problem with your structure : domainId can't be in character table. For me, it's a non-sens (except if it's for a perfomance issue, but your database must run before you know that or your have a good experience in database management). So may be, it's your thought, you need performance ?

My opinion, it's that the domainId must in a relation table (if there are more than one regent for a domain) or a regentid in the domain table (if there are only one regent), it's your choice but it's depend of what functionnality you want ? many regent for a domain or only one

I agree Arius...thanks for pointing that out!

Cross [pivot] table made most sense to me too...that's originally what I wanted, but when I was looking at birMail system databases.

The confusion stems from my trying to bring my plan closer to what you guys gave me in birMail style. I think you're completely right in stating their shouldn't be any ID's from other tables in a table (cross/pivot) every link....only exception I can really think of is where you're creating composite keys to stress identities etc.

Since we're slowing down on here this past two weeks, I might just go ahead & put my editor system to test...doing the database in cross tables makes sense too ... dyark if you are alright with it?

[Dyark]

no problem on my part!!

the database and my request works really well and fast, i created a dynamic php build up for my xml request so i can load what I want without creating xml files all the time.

I am also trying to understand custom map application to build and fill out my map.

[Mirviriam]

Yea, we probably won't see any drag on these databases...sql is very slick it's only when you get tons of tables & millions of entries that the over head gets to the server.

I was told by the DBA's at work, that for a low population game (less than 500 users per week on birthright.net?) & low intensity use (turn based game) we'd never push the server no matter how badly we ignored optimizing.

It was more that generating tables at each new creation would make the database's diverge drastically.

[Arjan]

here are some statistics from last month:
8,859 Visits
Previous: 8,529 (3.87%)

4,756 Absolute Unique Visitors
Previous: 4,733 (0.49%)

53,665 Pageviews
Previous: 55,330 (-3.01%)

6.06 Average Pageviews
Previous: 6.49 (-6.62%)

00:06:31 Time on Site
Previous: 00:06:37 (-1.67%)

47.91% Bounce Rate
Previous: 45.81% (4.58%)

47.97% New Visits
Previous: 50.09% (-4.22%)


even though search engines take up 60% of the bandwidth (and DB requests) the site still has no trouble.

[Mirviriam]

here are some statistics from last month:
8,859 Visits
Previous: 8,529 (3.87%)

4,756 Absolute Unique Visitors
Previous: 4,733 (0.49%)

53,665 Pageviews
Previous: 55,330 (-3.01%)

6.06 Average Pageviews
Previous: 6.49 (-6.62%)

00:06:31 Time on Site
Previous: 00:06:37 (-1.67%)

47.91% Bounce Rate
Previous: 45.81% (4.58%)

47.97% New Visits
Previous: 50.09% (-4.22%)


even though search engines take up 60% of the bandwidth (and DB requests) the site still has no trouble.

Just so we're clear...you're basically saying the server is a rock & 4 times more powerful than I claimed. Which means you feel we should keep the standard tables? Or does it mean you think we should generate a set of database tables for every game? Or do you plan on having a database made for each campaign, that connects to the master database with all the static tables?

(I have a hosted limit of 7 databases, so I can't design a DB for every campaign ran)

EDIT: clarified "...plan on having a database made for each campaign..." as before it sounded like just two databases for the final option.

[Arjan]

Just so we're clear...you're basically saying the server is a rock & 4 times more powerful than I claimed. Which means you feel we should keep the standard tables? Or does it mean you think we should generate a set of database tables for every game? Or do you plan on having a database made for each campaign, that connects to the master database with all the static tables?

meaning keeping the queries optimized a normal mysql server can handle A LOT


(I have a hosted limit of 7 databases, so I can't design a DB for every campaign ran)

EDIT: clarified "...plan on having a database made for each campaign..." as before it sounded like just two databases for the final option.

there is always the option for using table prefixes.

for example, birthright.net runs on 1 single DB.. but has the tables for the vbulletin and for the wiki (and some other tables i created in the past) ... and you can even make different DB-users and limit what they can see, update, create etc

for example, user1 sees all table with prefix camp1_
user2 sees all tables with prefix camp2_ etc etc

if you want to create your DB even more generic so it can be used in a multi campaign setting i think it requires an even more drastic redesign.

i think copying the tables and using a prefix would be an easier solution (and less complicated pivot table and queries)

[Dyark]

That's what I do, create 1 database with fixed tables for the beginning data and different pivot table for each campaign.

[Mirviriam]

meaning keeping the queries optimized a normal mysql server can handle A LOT


there is always the option for using table prefixes.

for example, birthright.net runs on 1 single DB.. but has the tables for the vbulletin and for the wiki (and some other tables i created in the past) ... and you can even make different DB-users and limit what they can see, update, create etc

for example, user1 sees all table with prefix camp1_
user2 sees all tables with prefix camp2_ etc etc

if you want to create your DB even more generic so it can be used in a multi campaign setting i think it requires an even more drastic redesign.

i think copying the tables and using a prefix would be an easier solution (and less complicated pivot table and queries)

When you had brought up views, that's when I started thinking putting the x_domain_campaign
x_province_campaign
idea in to serious consideration.

Mainly because with view's the sql queries aren't anymore complicated, the data sets aren't more complicated, it's just adding an index table essentially from what I can tell. The view option will hide the campaigns from each other.

I've never done more than one user table or setup multicampaign before...what makes this prefixing so much better?

EDIT: I went through & removed campaign_id from all tables in model data base ... also spent an hour fixing name & creating all the proper x_ & md_ tables. Sadly, that's more visible results than my sql spider & my province editor. I keep coming back to the fact I need more information about our layout before I can progress :)