Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2016

    Doubt on conceptual design of a database


    Im an Italian student. My computer teacher assigned me a homework, the design of a database, but according to him, my solution is wrong. Id like to know your opinion about this.

    Suppose a firm needs a database in order to manage the credits related to its clients. What is the best relationship between the table CREDITS and the table CLIENTS? According to my teacher is N:N, why?

  2. #2
    Join Date
    Apr 2014
    1 client has many credits. (not sure what N:N is) 1:M

  3. #3
    Join Date
    Nov 2004
    out on a limb
    you can have multiple customers
    you can have multiple credits

    a customer can have one or more credits

    when implementing a many to many realtionship in a realtionsl database you quite often do this via an intersection table
    a table for customers /* holds details of customers (eg org name)
    a table for credits /*holds detaisl of credits (nto to sure I'd do it this way in the real world
    a table for customers_credits /*holds details of credits allocated to customers.

    I guess it depends what 'credits' is meant to represent. it could be academic credits ir could be financial credits.

    if financial I'd expect a credit to be allocated to a specific transaction in the ledger AND to any relevant invoice(s). you'd need to see what settlements have been made on what invoices and from that understand what the outstanding balance on the customer account is.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2016
    The credits are financial, because they are related to a firm. I thought the relationship 1:N (N stands for more units, it's the same of 1:M) was the most reasonable. My teacher imposed me a relationship M: M between CUSTOMERS and CREDITS (with a resulting join table PAYMENTS), but it's a solution that I find very problematic.

    I think that a customer can have more credits, but a credit doesn't correspond to many customers. If I created the join table, I would have the primary key Client_Credit and the two foreign keys FK_Client and FK_Credit, that is, from the user side I would know that the firm collected, for example, from the "X client", say, 150 euro of a total credit of 500 €. But which utilities such kind of db can have for this firm?

    If I were a businesswoman, I would need to know the credit of each customer, and not one referring to a group of customers. If a customer pays me 90 days after and I have a trade credit, I need to know how much this customer owes to me individually. I could not have this information by creating a join table between Customers and Clients, neither from the individual tables where there would be personal data and total amounts respectively. Moreover in the hypothetical field "Payment Amount" of the join table, I might look just a part of the payment, that is, not necessarily the customer, only one, can pay all he/she owes me into one solution.

    At this point you might think to put a field "Single Amount" in the Customers table versus the "Total amount" in the credits table , even though I can note here a problem of redundancy because the debt is the credit. If there are many customers (so I have to type the individual amount every time), and if I was wrong, the total one would not be true, that is, the database would become inconsistent.

    Beyond that, which customers the credit is shared with? I could not know until all customers have paid their debts. And how the customers should be grouped for credit? On the basis of the typology, for a type of work done, etc.? You could create a table called TYPES, TYPE OF WORK, etc. and then between CUSTOMERS and TYPES you can put a foreign key in the table "CUSTOMERS"). So making a query, I would know what is the credit of all customers belonging to the same type and I would know how much they owe me altogether, too. At that point you might as well think a 1:M relationship between CREDITS and TYPES, for example.

    What do you think of this solution?
    Last edited by Italian_girl; 01-10-16 at 10:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts