Page 3 of 8 FirstFirst 1234567 ... LastLast
Results 21 to 30 of 76

Thread: SQL layout

  1. #21
    Senior Member Dyark's Avatar
    Join Date
    Jun 2007
    Location
    montreal, canada
    Posts
    163
    Downloads
    99
    Uploads
    2
    waiting ....

    PS do you know what the weighting in the table was for ??
    Last edited by Dyark; 07-23-2010 at 06:56 AM.

  2. #22
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,373
    Downloads
    48
    Uploads
    1
    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:
    Code:
    -- ----------------------------
    -- 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
    Attached Files Attached Files
    Te audire non possum. Musa sapientum fixa est in aure.

  3. #23
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,373
    Downloads
    48
    Uploads
    1
    Quote Originally Posted by Dyark View Post
    waiting ....

    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.
    Te audire non possum. Musa sapientum fixa est in aure.

  4. #24
    Senior Member Dyark's Avatar
    Join Date
    Jun 2007
    Location
    montreal, canada
    Posts
    163
    Downloads
    99
    Uploads
    2
    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 ???

  5. #25
    Senior Member Dyark's Avatar
    Join Date
    Jun 2007
    Location
    montreal, canada
    Posts
    163
    Downloads
    99
    Uploads
    2
    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)

  6. #26
    Senior Member Dyark's Avatar
    Join Date
    Jun 2007
    Location
    montreal, canada
    Posts
    163
    Downloads
    99
    Uploads
    2
    can you put your file as a sql file ?

  7. #27
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,373
    Downloads
    48
    Uploads
    1
    Quote Originally Posted by Dyark View Post
    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)
    Te audire non possum. Musa sapientum fixa est in aure.

  8. #28
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,373
    Downloads
    48
    Uploads
    1
    Quote Originally Posted by Dyark View Post
    can you put your file as a sql file ?
    just rename it to sql...
    Te audire non possum. Musa sapientum fixa est in aure.

  9. #29
    Senior Member Dyark's Avatar
    Join Date
    Jun 2007
    Location
    montreal, canada
    Posts
    163
    Downloads
    99
    Uploads
    2
    what time is it at your home ??

  10. #30
    Moo! Are you happy now? Arjan's Avatar
    Join Date
    Oct 2001
    Location
    Woerden, Netherlands
    Posts
    10,373
    Downloads
    48
    Uploads
    1
    Quote Originally Posted by Dyark View Post
    what time is it at your home ??
    it is now 22:45
    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.