Databases
StackOverflow2013
Postsdbo
PODatabase relationships and "structure"
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase relationships and "structure"
    primarykey
    data
    text
    <p>I am not very well versed in databases but if I have this setup:</p> <pre><code>Payment -&gt; Advances -&gt; Advance Payments </code></pre> <p>So, a <code>payment</code> can have an <code>advance</code> resulting from a shortage in the total amount due. Per the customer, when you want to make an <code>advance payment</code> you have to 'disguise' the <code>advance payment</code> as a <code>payment</code> - for their own accounting purposes. How would I go about that.</p> <p>Right now, <code>advances</code> have a foreign key to <code>payments</code> and <code>advance payments</code> have foreign keys to <code>advances</code>. </p> <p>A simple scenario:</p> <p>We have an order that totals $100. The customer pays $80 and $20 is advanced. So we have created an <code>advance</code> (advance 1) that is attached (fk'd) to this<code>payment</code> (payment 1). The customer orders again and the total is $50 this time and only pays $40 therefore another <code>advance</code> (advance 2) is created tied to that new <code>payment</code> (payment 2). </p> <p>When the customer decides they want to pay off the <code>advances</code> a <code>payment</code> is created (payment 3) and the <code>advances</code> (advances 1 and advance 2) are added to that <code>payment</code> (payment 3). This is done so that when it gets displayed in the system the employee has the option of choosing which <code>advance</code> to make the payment to. Its not as simple as getting the total and then subtracting the <code>payment</code> amount. </p> <p>So when they enter in the amount an <code>advance payment</code> is created and is attached to that <code>advance</code> because the <code>advance payments</code> are fk'd to <code>advances</code>.</p> <p><strong>Now the question, if I want to see what advance payments were created by a payment how would i do that?</strong></p> <p>I don't want to break my structure of <code>payment - advance - advance payment</code> - <em>or do I?</em> I thought about just having <code>paymentids</code> in the <code>advance payment</code> table but that would create a relationship between <code>payments</code> and <code>advance payments</code> that neglects the <code>advance</code> that is the middle man. I may be thinking about this incorrectly but I am not sure.</p> <p>Some more info:</p> <pre><code>Payment Table ------------- id : pk amount Advance Table ------------- id amount paymentId // payment that created the advance Advance Payment Table --------------------- id amount advanceId </code></pre> <p>What im proposing in which I think is wrong is:</p> <pre><code>Payment Table ------------- id : pk amount Advance Table ------------- id amount paymentId // payment that created the advance Advance Payment Table --------------------- id amount advanceId paymentId &lt;---- add paymentid </code></pre> <p>I think this would break the structure/flow and a payment will no longer have an advance that has advance payments.</p> <p><em>A little more info - we are using linq and SQL Server 2008. I don't know if that makes a difference.</em></p> <p>This maybe very confusing but I have tried to explain it the best way possible.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser2238556
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThis looks like a overly complicated structure. I think you could do with `Order`s and `Payment`s only, like journal entries. At first, an order has a negative payment, the order amount. An advance is the difference between the first negative payment and subsequent payments.
      singulars
      1. PODatabase relationships and "structure"
      1. USGert Arnold
    2. COthe only issue is that we have to keep track of advances because a customer could have multiple advances...and could potentially pay one advance and not the other and we would need to know which advance he is paying. I think that would be a nightmare to track without a separate table
      singulars
      1. PODatabase relationships and "structure"
      1. USuser2238556
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload