CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Chris Hodges
Chris Hodges

Reputation: 73

Tuning database structure or SQL query to allow for specific output

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.

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.

I've run into a problem that has stumped me for the moment

I have the following table structure

**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

I'd like to be able to show the following information a page showing the balance:

<b>Total Balance</b>

<p> Day costs: </p>
<p> Blue Fish cost: </p>
<p> Pink Fish cost: </p>
<p> Red Fish cost: </p>
<p> Green Fish cost: </p>
<p> Total: </p>

<b> Balance Owing :</b>

<p> Day costs total: </p>
<p> Blue Fish total: </p>
<p> Pink Fish total: </p>
<p> Red Fish total: </p>
<p> Green Fish total: </p>
<p> Total: </p>

In order to display the above information, I figured I could:

At this point so far, I think my structure is fine to do all of the above.

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.

If possible, for each cost maybe put the number of fish in brackets, e.g.:

<p> Red Fish total: $160 (4) </p>

Do I need to radically change my table structure? Would adding an amountPaid column to my customers table be sufficient?

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?

What are the drawbacks of such a method and what are some better approaches, in line with better practices?

Upvotes: 0

Views: 97

Answers (1)

StudyOfCrying
StudyOfCrying

Reputation: 530

Do your customers only make a single payment ever? If not, you do not want to add amountPaid to your customers table. What you want to do most likely is to store payment information in an entirely separate table. You already have an orders table which is fine for storing your orders, but you do not have any way to tie a customer to an order.

I don't know exactly what you're trying to do, but I'd start with the following:

  • Add a customerId to your orders table.

  • Add the total amount owed to your orders table as well as a remaining amount due (I'm assuming this amount is based on the cost of the fish at the time the order is placed; i.e. not impacted by changes to fish prices in the future).

  • Add a payment table that has a customerId, an orderId, and contains the amount paid and/or due. Use this to track payments from the customers. You can optionally add a status field to the orders table and update it to paid when this has been fulfilled (or conditionally rely on the amount remaining being zero).

Upvotes: 1

Related Questions