Data Model

The below XML is a representation for the Data Model for the Stock application.  This XML will be used to create the following:

• Data Access Objects
• Repositories
• ERD (for documentation purposes)
• Create/update databases themselves

<?xml version="1.0" encoding="UTF-8"?>
<database name="Stock">
  <table name="current_stock" engine="Innodb">
    <column>
      <name>id</name>
      <type>int</type>
      <isNull>false</isNull>
      <key>PRIMARY</key>
      <autoIncrement>true</autoIncrement>
    </column>
    <column>
      <name>stock_symbol</name>
      <type>varchar(5)</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>last_price</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>ask_price</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>bid_price</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>change_from_close</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>open_price</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>close_price</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>change_from_previous</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>stock_timestamp</name>
      <type>datetime</type>
      <isNull>true</isNull>
    </column>
  </table>
  <table name="stock_history" engine="Innodb">
    <column>
      <name>id</name>
      <type>bigint</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>stock_symbol</name>
      <type>varchar(5)</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>last_price</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>plus_minus</name>
      <type>enum</type>
      <values>
        <value>PLUS</value>
        <value>MINUS</value>
        <value>SAME</value>
      </values>
      <isNull>true</isNull>
    </column>
    <column>
      <name>percent_change</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>date_inserted</name>
      <type>timestamp</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>stock_timestamp</name>
      <type>timestamp</type>
      <isNull>false</isNull>
      <default>CURRENT_TIMESTAMP</default>
    </column>
  </table>
  <table name="stock_history_archive" engine="Innodb">
    <column>
      <name>id</name>
      <type>bigint</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>stock_symbol</name>
      <type>varchar(5)</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>last_price</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>plus_minus</name>
      <type>enum</type>
      <values>
        <value>PLUS</value>
        <value>MINUS</value>
        <value>SAME</value>
      </values>
      <isNull>true</isNull>
    </column>
    <column>
      <name>percent_change</name>
      <type>float</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>date_inserted</name>
      <type>timestamp</type>
      <isNull>true</isNull>
    </column>
    <column>
      <name>stock_timestamp</name>
      <type>timestamp</type>
      <isNull>false</isNull>
      <default>CURRENT_TIMESTAMP</default>
    </column>
  </table>
  <table name="stock_transaction" engine="Innodb">
    <column>
      <name>id</name>
      <type>bigint</type>
      <isNull>false</isNull>
      <key>PRIMARY</key>
      <autoIncrement>true</autoIncrement>
    </column>
    <column>
      <name>stock_symbol</name>
      <type>varchar(5)</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>number_of_shares</name>
      <type>smallint</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>bought_price</name>
      <type>float</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>sold_price</name>
      <type>float</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>investment</name>
      <type>float</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>gain_loss</name>
      <type>float</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>bought_stock_timestamp</name>
      <type>timestamp</type>
      <isNull>false</isNull>
      <default>CURRENT_TIMESTAMP</default>
    </column>
    <column>
      <name>sold_stock_timestamp</name>
      <type>timestamp</type>
      <isNull>false</isNull>
      <default>CURRENT_TIMESTAMP</default>
    </column>
    <column>
      <name>algorithm_id</name>
      <type>tinyint</type>
      <isNull>false</isNull>
    </column>
  </table>
  <table name="bought_stock" engine="Innodb">
    <column>
      <name>id</name>
      <type>bigint</type>
      <isNull>false</isNull>
      <key>PRIMARY</key>
      <autoIncrement>true</autoIncrement>
    </column>
    <column>
      <name>stock_symbol</name>
      <type>varchar(5)</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>number_of_shares</name>
      <type>smallint</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>price_bought</name>
      <type>float</type>
      <isNull>false</isNull>
    </column>
    <column>
      <name>stock_timestamp</name>
      <type>timestamp</type>
      <isNull>false</isNull>
      <default>CURRENT_TIMESTAMP</default>
    </column>
    <column>
      <name>algorithm_id</name>
      <type>tinyint</type>
      <isNull>true</isNull>
    </column>
  </table>
</database>