Page 1 of 8 12345 ... LastLast
Results 1 to 10 of 76

Thread: SQL layout

  1. #1
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    249
    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

  2. #2
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    249
    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

  3. #3
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,307
    Downloads
    39
    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.

  4. #4
    Senior Member Dyark's Avatar
    Join Date
    Jun 2007
    Location
    montreal, canada
    Posts
    163
    Downloads
    69
    Uploads
    2
    tell use exactly what you want to change and we can do it (a few minutes - hours) for me !

  5. #5
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,307
    Downloads
    39
    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 finished
    Te audire non possum. Musa sapientum fixa est in aure.

  6. #6
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,307
    Downloads
    39
    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 voila
    Last edited by Arjan; 07-20-2010 at 10:57 PM.
    Te audire non possum. Musa sapientum fixa est in aure.

  7. #7
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    249
    Downloads
    4
    Uploads
    0
    Quote Originally Posted by Arjan View Post
    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?
    Legacy of Kings: Member

  8. #8
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    249
    Downloads
    4
    Uploads
    0
    Quote Originally Posted by Arjan View Post
    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.
    Legacy of Kings: Member

  9. #9
    Senior Member Mirviriam's Avatar
    Join Date
    Apr 2008
    Location
    Where the moon cuts the wind.
    Posts
    249
    Downloads
    4
    Uploads
    0
    Quote Originally Posted by Arjan View Post
    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).
    Legacy of Kings: Member

  10. #10
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,307
    Downloads
    39
    Uploads
    1
    Quote Originally Posted by Mirviriam View Post
    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)
    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)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
BIRTHRIGHT, DUNGEONS & DRAGONS, D&D, the BIRTHRIGHT logo, and the D&D logo are trademarks owned by Wizards of the Coast, Inc., a subsidiary of Hasbro, Inc., and are used by permission. ©2002-2010 Wizards of the Coast, Inc.