Databases
StackOverflow2013
Postsdbo
POTuning database structure or SQL query to allow for specific output
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTuning database structure or SQL query to allow for specific output
    primarykey
    data
    text
    <p>I'm creating a pretty simple app from scratch to allow things to be ordered, and then the costs for each customer to be shown.</p> <p>It's my first foray into programming, so I'm trying to do my best to make sense of a lot of stuff and follow best practices where possible.</p> <p>I've run into a problem that has stumped me for the moment</p> <p>I have the following table structure</p> <pre><code>**Customers:** id firstName lastName country days ---------------------------------------- 1, john, smith, usa, 5 **Items:** itemID, itemName, itemCost ------------------------------ 1, blueFish, 20 2, dayCost, 10 **Purchases:** id numBlueFish numPinkFish numRedFish numGreenFish -------------------------------------------------- 1, 0, 0, 2, 4 </code></pre> <p>I'd like to be able to show the following information a page showing the balance:</p> <pre><code>&lt;b&gt;Total Balance&lt;/b&gt; &lt;p&gt; Day costs: &lt;/p&gt; &lt;p&gt; Blue Fish cost: &lt;/p&gt; &lt;p&gt; Pink Fish cost: &lt;/p&gt; &lt;p&gt; Red Fish cost: &lt;/p&gt; &lt;p&gt; Green Fish cost: &lt;/p&gt; &lt;p&gt; Total: &lt;/p&gt; &lt;b&gt; Balance Owing :&lt;/b&gt; &lt;p&gt; Day costs total: &lt;/p&gt; &lt;p&gt; Blue Fish total: &lt;/p&gt; &lt;p&gt; Pink Fish total: &lt;/p&gt; &lt;p&gt; Red Fish total: &lt;/p&gt; &lt;p&gt; Green Fish total: &lt;/p&gt; &lt;p&gt; Total: &lt;/p&gt; </code></pre> <p>In order to display the above information, I figured I could:</p> <ul> <li>Calculate the day cost by multiplying customers.days by items.dayCost</li> <li>Calculate the cost of each fish by multiplying customers.numColorFish by items.colorFish</li> <li>Calculate a total by multiplying each customers.numColorFish by it's corresponding items.colorFish, adding them together and then adding that to the result of of customers.days by items.dayCost</li> </ul> <p>At this point so far, I think my structure is fine to do all of the above.</p> <p>What I would like to be able to do now is pay an arbitrary amount, and show the balance remaining to be paid, and the total costs.</p> <p>If possible, for each cost maybe put the number of fish in brackets, e.g.:</p> <pre><code>&lt;p&gt; Red Fish total: $160 (4) &lt;/p&gt; </code></pre> <p>Do I need to radically change my table structure? Would adding an amountPaid column to my customers table be sufficient? </p> <p>Should I have a table to record every time any payment is made? Would that make it more complicated to output how much a customer has paid/owes?</p> <p>What are the drawbacks of such a method and what are some better approaches, in line with better practices?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USChris Hodges
    1. USChris Hodges
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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