samedi 6 juin 2015

Modelling One-to-Many relationship in SQLite

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:

Debtor table

and the debt table: enter image description here

My questions are these:

  1. 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?
  2. 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