Results 1 to 10 of 76
Thread: SQL layout
-
05-07-2010, 10:49 AM #1
- Join Date
- Apr 2008
- Location
- Where the moon cuts the wind.
- Posts
- 259
- Downloads
- 4
- Uploads
- 0
SQL layout
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/ind...iriam/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.Legacy of Kings: Member
-
05-24-2010, 04:37 AM #2
- Join Date
- Apr 2008
- Location
- Where the moon cuts the wind.
- Posts
- 259
- Downloads
- 4
- Uploads
- 0
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.Legacy of Kings: Member
-
07-20-2010, 06:27 AM #3
- Join Date
- Oct 2001
- Location
- Woerden, Netherlands
- Posts
- 10,373
- Downloads
- 48
- Uploads
- 1
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..Last edited by Arjan; 07-20-2010 at 06:31 AM.
Te audire non possum. Musa sapientum fixa est in aure.
-
07-20-2010, 09:15 AM #4
tell use exactly what you want to change and we can do it (a few minutes - hours) for me !
-
07-20-2010, 09:31 PM #5
- Join Date
- Oct 2001
- Location
- Woerden, Netherlands
- Posts
- 10,373
- Downloads
- 48
- Uploads
- 1
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 finishedTe audire non possum. Musa sapientum fixa est in aure.
-
07-20-2010, 10:45 PM #6
- Join Date
- Oct 2001
- Location
- Woerden, Netherlands
- Posts
- 10,373
- Downloads
- 48
- Uploads
- 1
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 voilaLast edited by Arjan; 07-20-2010 at 10:57 PM.
Te audire non possum. Musa sapientum fixa est in aure.
-
07-21-2010, 06:47 PM #7
- Join Date
- Apr 2008
- Location
- Where the moon cuts the wind.
- Posts
- 259
- Downloads
- 4
- Uploads
- 0
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?Legacy of Kings: Member
-
07-21-2010, 06:56 PM #8
- Join Date
- Apr 2008
- Location
- Where the moon cuts the wind.
- Posts
- 259
- Downloads
- 4
- Uploads
- 0
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.Legacy of Kings: Member
-
07-21-2010, 06:59 PM #9
- Join Date
- Apr 2008
- Location
- Where the moon cuts the wind.
- Posts
- 259
- Downloads
- 4
- Uploads
- 0
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).
Legacy of Kings: Member
-
07-21-2010, 11:48 PM #10
- Join Date
- Oct 2001
- Location
- Woerden, Netherlands
- Posts
- 10,373
- Downloads
- 48
- Uploads
- 1
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)Te audire non possum. Musa sapientum fixa est in aure.
Thread Information
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks