I have two tables in my database; debt and debtor. I want to have just one row representing a particular debtor and then update the columns (totalDebts, totalAmountOwed) when ever a new debt is added. Debt table can contain many columns referencing a particular debtor. I have used the phone number provided when adding a debt as the primary key of the debt and debtor tables and the tables are added without issues:
try {
debt.setCustomerName(params[0]);
debt.setCustomerPhone(params[1]);
debt.setItemName(params[2]);
debt.setUnitPrice(Double.parseDouble(params[3]));
debt.setItemQuantity(Integer.parseInt(params[4]));
debt.setAmountPaid(Double.parseDouble(params[5]));
debt.setAmountDue(Double.parseDouble(params[6]));
debt.setDueDate(params[7]);
debtor.setDebtorName(params[0]);
debtor.setPhoneNumber(params[1]);
debtor.setTotalAmountOwed(Double.parseDouble(params[6]));
debtor.setTotalDebts(0);
databaseHandler.addDebt(debt);
databaseHandler.addDebtor(debtor);
dabaseHandler.close(); //close the database connection
This is the result when I add records:
and the debt table:
My questions are these:
- Since I can't add two debtor records with the same phone numbers as Id, how do I just update the row when a debtor record already exist?
- How do I create a relationship between these two tables so I can run queries to fetch debts that belong to a specific debtor?
Any pseudocode to help me would be greatly appreciated.
Aucun commentaire:
Enregistrer un commentaire