Databases
StackOverflow2013
Postsdbo
POTracking monthly call count per individual - MySQL or XML?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTracking monthly call count per individual - MySQL or XML?
    text
    copied!<p>I am trying to determine the best way to store a monthly call tally for around 43 personnel. The client wants to be able to view the data in several different ways - who has the most/least calls YTD, graphs of the top 10, change from the pervious year, etc.</p> <p>I considered trying to continuously update an XML file for each year with each individual's name and monthly call tally but that would make it more challenging to extract different types of data than if I were to use MySQL.</p> <p>If MySQL is the favorable choice, would it be best to store the data with a single row per year per person with a column in each for every month of the year? Or, every month, add a row with the year, month, and call tally?</p> <p>Advice would be appreciated.</p> <p>Here is a sample of the XML file that contains the data -</p> <pre><code>&lt;?xml version="1.0" encoding="UTF-8" ?&gt; &lt;report&gt; &lt;ReportHeader&gt; &lt;Section SectionNumber="0"&gt; &lt;Picture Name="DeptPic1" GraphicType="BolbField"&gt;&lt;/Picture&gt; &lt;/Section&gt; &lt;/ReportHeader&gt; &lt;Group Level="1"&gt; &lt;GroupFooter&gt; &lt;Section SectionNumber="1"&gt; &lt;Field Name="Field11" FieldName="{IncdPers.PERSONNAME}"&gt; &lt;FormattedValue&gt;Captain John Doe&lt;/FormattedValue&gt; &lt;Value&gt;Captain John Doe&lt;/Value&gt; &lt;/Field&gt; &lt;Field Name="Field12" FieldName="{IncdPers.PERSONLOOKUPID}"&gt; &lt;FormattedValue&gt;Doe, John&lt;/FormattedValue&gt; &lt;Value&gt;Doe, John&lt;/Value&gt; &lt;/Field&gt; &lt;Field Name="Field13" FieldName="DistinctCount ({In5basic.GUIDIDNUMBER}, {IncdPers.PERSONLOOKUPID})"&gt; &lt;FormattedValue&gt;6&lt;/FormattedValue&gt; &lt;Value&gt;6&lt;/Value&gt; &lt;/Field&gt; &lt;Field Name="Field14" FieldName="{@PercentInc}"&gt; &lt;FormattedValue&gt;54.55&lt;/FormattedValue&gt; &lt;Value&gt;54.55&lt;/Value&gt; &lt;/Field&gt; &lt;Text Name="Text14"&gt; &lt;TextValue&gt;%&lt;/TextValue&gt; &lt;/Text&gt; &lt;/Section&gt; &lt;/GroupFooter&gt; &lt;/Group&gt; &lt;Group Level="1"&gt; &lt;GroupFooter&gt; &lt;Section SectionNumber="1"&gt; &lt;Field Name="Field11" FieldName="{IncdPers.PERSONNAME}"&gt; &lt;FormattedValue&gt;Firefighter Jane Smith&lt;/FormattedValue&gt; &lt;Value&gt;Firefighter Jane Smith&lt;/Value&gt; &lt;/Field&gt; &lt;Field Name="Field12" FieldName="{IncdPers.PERSONLOOKUPID}"&gt; &lt;FormattedValue&gt;Smith, Jane&lt;/FormattedValue&gt; &lt;Value&gt;Smith, Jane&lt;/Value&gt; &lt;/Field&gt; &lt;Field Name="Field13" FieldName="DistinctCount ({In5basic.GUIDIDNUMBER}, {IncdPers.PERSONLOOKUPID})"&gt; &lt;FormattedValue&gt;1&lt;/FormattedValue&gt; &lt;Value&gt;1&lt;/Value&gt; &lt;/Field&gt; &lt;Field Name="Field14" FieldName="{@PercentInc}"&gt; &lt;FormattedValue&gt;9.09&lt;/FormattedValue&gt; &lt;Value&gt;9.09&lt;/Value&gt; &lt;/Field&gt; &lt;Text Name="Text14"&gt; &lt;TextValue&gt;%&lt;/TextValue&gt; &lt;/Text&gt; &lt;/Section&gt; &lt;/GroupFooter&gt; &lt;/Group&gt; &lt;/report&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload