Best database layout for an xml format?



  • I'm parsing some Dwarf Fortress data. Each <historical_event> element can contain a bunch of fields.

    ida unique identifier
    yearan integer, obviously
    seconds7250 seconds in an hour, 24 hours in a day, 28 days in a month, 12 months in a year
    typethe type of event, such as "change hf state"

    Those are the easy ones. They all appear in every single event. Here's the rest:

    hfid
    state
    site_id
    subregion_id
    feature_layer_id
    coords
    civ_id
    site_civ_id
    hfid_target
    structure_id
    target_hfid
    snatcher_hfid
    changee_hfid
    changer_hfid
    old_race
    old_caste
    new_race
    new_caste
    subtype
    group_1_hfid
    group_2_hfid
    slayer_hfid
    slayer_race
    slayer_caste
    slayer_item_id
    slayer_shooter_item_id
    cause
    woundee_hfid
    wounder_hfid
    group_hfid
    escape
    return
    attacker_civ_id
    defender_civ_id
    attacker_general_hfid
    defender_general_hfid
    secret_goal
    body_state
    building_id
    entity_id
    wcid
    master_wcid
    site_id1
    site_id2
    hist_fig_id
    doer_hfid
    interaction
    situation
    reason
    new_site_civ_id
    new_leader_hfid
    hist_figure_id
    law_add

    Here are some examples:

    <historical_event>
            <id>450887</id>
            <year>1</year>
            <seconds72>67200</seconds72>
            <type>item stolen</type>
    </historical_event>
    <historical_event>
            <id>450888</id>
            <year>1</year>
            <seconds72>75600</seconds72>
            <type>changed creature type</type>
            <changee_hfid>73011</changee_hfid>
            <changer_hfid>72996</changer_hfid>
            <old_race>DWARF</old_race>
            <old_caste>FEMALE</old_caste>
            <new_race>NIGHT_CREATURE_9</new_race>
            <new_caste>FEMALE</new_caste>
    </historical_event>
    <historical_event>
            <id>450889</id>
            <year>1</year>
            <seconds72>117600</seconds72>
            <type>add hf entity link</type>
            <civ_id>3027</civ_id>
    </historical_event>
    

    P.S. the first event starts on line 62341010 of the xml file.


  • Trolleybus Mechanic

     Does your database support any sort of XML datatype?



  • @Lorne Kates said:

     Does your database support any sort of XML datatype?


    It's postgresql, but I'm kind of hoping I can keep it easily queryable, like "find me all events related to this site/entity/figure" or "find me events in this time period". Also, I'm not sure if rails supports xml datatypes.


  • Trolleybus Mechanic

    @Ben L. said:

    but I'm kind of hoping I can keep it easily queryable
     

    Then just make a big flat table with one column for each attribute. That's the easiest.



  • Ok, just so everyone sees how bad this format is, here's the command to create the model in rails:

    rails g model event year:integer:index seconds:integer kind:string:index figure:references state:string site:references region:references feature_layer_id:integer:index x:integer y:integer civ_id:integer:index site_civ_id:integer:index target_id:integer:index structure_id:integer:index snatcher_id:integer:index changee_id:integer:index changer_id:integer:index old_caste_id:integer:index new_caste_id:integer:index subtype:string:index figure1_id:integer:index figure2_id:integer:index slayer_id:integer:index slayer_item_id:integer:index slayer_shooter_item_id:integer:index cause:string:index woundee_id:integer:index wounder_id:integer:index escape:boolean return:boolean attacker_id:integer:index defender_id:integer:index attacker_general_id:integer:index defender_general_id:integer:index secret_goal:string:index body_state:string:index building_id:integer:index entity:references construction_id:integer:index master_construction_id:integer:index site1_id:integer:index site2_id:integer:index interaction:string:index situation:string:index reason:string:index new_site_civ_id:integer:index new_leader_id:integer:index

  • Discourse touched me in a no-no place

    @Lorne Kates said:

    Then just make a big flat table with one column for each attribute. That's the easiest.
    Only if you use Excel…


  • Trolleybus Mechanic

    @dkf said:

    @Lorne Kates said:
    Then just make a big flat table with one column for each attribute. That's the easiest.
    Only if you use Excel…
    And then you can use Excel to recreate the game engine, using the data in the very same spreadsheet.

    Get on it, Ben.


  • Discourse touched me in a no-no place

    @Ben L. said:

    seconds7250 seconds in an hour, 24 hours in a day, 28 days in a month, 12 months in a year
    Erm… was that a tyop? Either of them?


  • @PJH said:

    @Ben L. said:

    seconds7250 seconds in an hour, 24 hours in a day, 28 days in a month, 12 months in a year
    Erm… was that a tyop? Either of them?

    403200 seconds in a year. Not a typo.


  • Discourse touched me in a no-no place

    @Ben L. said:

    @PJH said:
    @Ben L. said:

    seconds7250 seconds in an hour, 24 hours in a day, 28 days in a month, 12 months in a year
    Erm… was that a tyop? Either of them?

    403200 seconds in a year.

    Just wondered what happened to minutes.@Ben L. said:
    Not a typo.
    tyop. Mine wasn't a typo.



  • @Ben L. said:

    @PJH said:
    @Ben L. said:

    seconds7250 seconds in an hour, 24 hours in a day, 28 days in a month, 12 months in a year

    Erm… was that a tyop? Either of them?

    403200 seconds in a year. Not a typo.

     

    Wait, I thought there were 31,557,600 seconds in a year (1/4 day per year added so as not to confuse people with leap year calculations), and even adding the seconds * minutes what you have above only equates to 24,192,000 seconds in a year.  (50 seconds * 60 Minutes * 24 Hours * 28 Days * 12 Months).

     

    Ah, I see, you have it on M for minutes when you should have it on W for Wumbo.

     



  • @Medezark said:

    @Ben L. said:

    @PJH said:
    @Ben L. said:

    seconds7250 seconds in an hour, 24 hours in a day, 28 days in a month, 12 months in a year

    Erm… was that a tyop? Either of them?

    403200 seconds in a year. Not a typo.

     

    Wait, I thought there were 31,557,600 seconds in a year (1/4 day per year added so as not to confuse people with leap year calculations), and even adding the seconds * minutes what you have above only equates to 24,192,000 seconds in a year.  (50 seconds * 60 Minutes * 24 Hours * 28 Days * 12 Months).

     

    Ah, I see, you have it on M for minutes when you should have it on W for Wumbo.

     

    Have you not played Dwarf Fortress? It goes quite a bit faster than real time.

    By the way, if you haven't played Dwarf Fortress, do it. It's free.



  • @Ben L. said:

    By the way, if you haven't played Dwarf Fortress, do it. It's free.

    It's shit. Save your time.

    Games are supposed to be fun. Not super-detailed simulations of fantasy race fingernail growth which, inexplicably, has ZERO graphics.



  • @blakeyrat said:

    Games are supposed to be fun. Not super-detailed simulations of fantasy race fingernail growth which, inexplicably, has ZERO graphics.
     

    Dwarf Fortress gets closer to the Spreadsheet-management RPG platonic ideal.



  • @dhromed said:

    Dwarf Fortress gets closer to the Spreadsheet-management RPG platonic ideal.

    Ideal to whom? The most boring man alive? Oh wait, I know: that asshole who spends all his time in Minecraft making scientific calculators out of blocks!


  • ♿ (Parody)

    @blakeyrat said:

    Games are supposed to be fun.

    And one has ever disagreed about what constitutes fun. I'd sooner start all of my sentences by yelling "Alahu Akhbar" than rely on your idea of fun.



  • @blakeyrat said:

    Ideal to whom?
     

    To Plato, the philosopher after whom the expression is named.

    Also, humor.



  • @boomzilla said:

    I'd sooner start all of my sentences by yelling "Alahu Akhbar" than rely on your idea of fun.
     

    Yeah, but you watch America's Funniest Home Videos.


  • ♿ (Parody)

    @dhromed said:

    @boomzilla said:
    I'd sooner start all of my sentences by yelling "Alahu Akhbar" than rely on your idea of fun.

    Yeah, but you watch America's Funniest Home Videos.

    Allahu Akhbar! Exactly!



  • @blakeyrat said:

    @Ben L. said:
    By the way, if you haven't played Dwarf Fortress, do it. It's free.

    It's shit. Save your time.

    Games are supposed to be fun. Not super-detailed simulations of fantasy race fingernail growth which, inexplicably, has ZERO graphics.

    Dwarf Fortress is fun



  • @blakeyrat said:

    ZERO graphics.
     

    I found out there are texture packs or skins or graphics sets or however you'd want to call them that will make the game more palatable to the discerning eye.


  • ♿ (Parody)

    @Ben L. said:

    @blakeyrat said:
    @Ben L. said:
    By the way, if you haven't played Dwarf Fortress, do it. It's free.

    It's shit. Save your time.

    Games are supposed to be fun. Not super-detailed simulations of fantasy race fingernail growth which, inexplicably, has ZERO graphics.

    Dwarf Fortress is fun

    Alahu Akhbar! There's no accounting for taste. You have to remember that blakeyrat (PBUH) doesn't like to read long or dense books. Which is fine for him, but those sorts of books can be some of the most rewarding books for me. I've always thought that Dwarf Fortress would be really cool to get into if I had the time for games any more. It makes me think of the role playing game from Cryptonomicon in the insane attention to detail. In an earlier era, blakey would have excoriated you for playing Zork instead of Pacman.


Log in to reply