Home » SQL For Programmers » SQL For Programmers – New Question

SQL For Programmers – New Question

tp_vol3_007 I got this question last week that I thought would be worth discussing on the blog.

I would like to create a table that lists contributions by date, amount, and designation. In some cases one gift was split two (or more) ways. For example, a contribution of $1000 was given, and $500 went to the scholarship fund and $500 to athletics. The gift date would be the same for both gifts, but they must be recorded separately in order to split the gift. In my new table, I want to list them in rows like this:
date| amount| designation1, designation2, designation3, etc.

Would it be best to use a cursor or a stored procedure. I’ve never done anything like this before, so I’m not sure where to begin.


Um… using cursors WOULD be in a stored procedure, right?  But I think we are getting ahead of ourselves.

When I get these kinds of questions, I always have to put together the hidden pieces.  So I’m sorry if some of what I cover isn’t part of the question, but it is at least part of the answer.

First, let’s take a look at how you would store this kind of data in a database.  You should have two tables.  The first would be the transaction information:

FieldDescription
IDUnique transaction ID, probably auto incrementing integer.
DateDate the gift was given

The second table would hold the split information

FieldDescription
IDUnique split ID, also probably auto incrementing integer
TranactionIDLinks the record to the transaction row (above)
AmountThe amount of the split
DescriptionDescription of the split
  

 

I’m not sure if the person who originally asked me the question was meaning to place the transaction and split information all in the same row in the same table or not, but I’ve seen enough new developers try to do this to at least make me aware that this is a possibility.

The reason you don’t want to put it in there, even if you could pre-determine the maximum number of splits, is because at some point in the future, there is a very real likelihood that the number will grow.  My rule is, “if there is N of something, where N > 1, then there will be N + 1 of that same thing.”  Therefore, you always write your code to handle multiple items as soon as you know there will be more than one.  It takes no longer to code and will more likely than not save you time in the future.

For the very same reason, you don’t want to display it as though it all came out of one row, either.  You’d either need to dynamically create your display to show multiple columns as the maximum number grew, or you’d need to modify the code as it grew.  Neither is a very good solution.

So how would you display this information, then?

As a master detail report

Date   Total
           Amount    Description

DateTotal
AmountDescription
05/06/09$100.00 
 $50.00Fund One
 $50.00Fund Two
   
05/07/09$75.00 
 $75.00Some place else

 

Store procedure or cursor?  You should be able to produce a report like this using two store procedures–one to get the list of transactions and another to get the list of splits for a specific transaction.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • SQL CURSOR – SQL For ProgrammersSQL CURSOR – SQL For Programmers One of the things it took me a while to figure out was how to get a stored procedure to loop through data in a table and pull out specific data I needed.There are actually two parts […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • Random in SQL – SQL For ProgrammersRandom in SQL – SQL For Programmers Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database.Naturally, as a programmer, I started looking for the SQL […]
  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to […]
  • SQL For Programmers – Finding IN a ListSQL For Programmers – Finding IN a List In the last SQL post, we looked at looking for content that was LIKE other content.  While this has its uses, it is limited in its ability to find more than one pattern.  So what if we […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.