-1

Please observe the image below, is this approach correct or is there a way to achieve my scope?

enter image description here

Settlement of the payment may come several ways

  1. by cash

  2. by Check

  3. Maybe the customer made a return of goods whereas the invoice may have been settled with credit note (Return TYPE3 Table)

At times it may have all three types of settlement per invoice, if that is the case how do I normalize.

I'm confused, please advise.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Prem
  • 31
  • 8
  • 2
    I would guess that your three type tables ought to reference the parent settlement table. It looks like you may have got the relationship "wrong way round". Unfortunately your diagram isn't very clear because you have used a non-standard notation. – nvogel Oct 08 '18 at 21:13
  • @sqlvogel you are absolutely correct, any advice on how to relate the three kinds of child table to the parent? – Prem Oct 08 '18 at 23:19
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 09 '18 at 00:28
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. PS "Unfortunately your diagram isn't very clear" suggests that you should edit your question to be clear if you want an answer. – philipxy Oct 09 '18 at 00:28
  • Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. PS Why do you say "normalize"? If you want to normalize, normalize. "Normalize" doesn't mean "make a good design". PS Please clarify via post edits, not comments. – philipxy Oct 09 '18 at 01:03

1 Answers1

1

I think you want something like this example:

CREATE TABLE Settlements (ID INT PRIMARY KEY, ...);
CREATE TABLE Returns (ID INT PRIMARY KEY REFERENCES Settlements (ID), ...);
CREATE TABLE Cash (ID INT PRIMARY KEY REFERENCES Settlements (ID), ...);
CREATE TABLE Checks (ID INT PRIMARY KEY REFERENCES Settlements (ID), ...);
nvogel
  • 24,981
  • 1
  • 44
  • 82
  • Thanks for all the feedback, but I need your advice to see if the design model is legit – Prem Oct 14 '18 at 06:18
  • You asked for advice on how to relate three child tables to one parent. That's what my answer is trying to help you with - three tables referencing the settlements table. I already commented on how I think your diagram is wrong. – nvogel Oct 14 '18 at 07:26