SQL For Programmers – New Question
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:
| Field | Description |
| ID | Unique transaction ID, probably auto incrementing integer. |
| Date | Date the gift was given |
The second table would hold the split information
| Field | Description |
| ID | Unique split ID, also probably auto incrementing integer |
| TranactionID | Links the record to the transaction row (above) |
| Amount | The amount of the split |
| Description | Description 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
| Date | Total | |
| Amount | Description | |
| 05/06/09 | $100.00 | |
| $50.00 | Fund One | |
| $50.00 | Fund Two | |
| 05/07/09 | $75.00 | |
| $75.00 | Some 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.
Other post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding a String - September 3rd, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedures (Better than LINQ) - September 10th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- SQL WHILE - SQL For Programmers - October 2nd, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
Other Related Items:
Gyration Air Mouse Elite In-Air Cursor Control using Patented MotionSenseTM Technology and Optical Sensor with Low Profile Keyboard GYM5600LKNAThe elegant and ergonomic Air Mouse Elite brings the intuitive simplicity of motion to your digital life. With the Air Mouse Elite, your natural hand ... Read More >
07-10 CHEVY TAHOE CHROME FENDER TRIM Wheel Well Molding 2WD 4WD 2007 2008 2009 2010 ChevroletSet of 4 chrome wheel well moldings. They are about 1.5 inches wide and you can install them using either mounting clips, mounting screws or both. They will not fit if your truck has any kind of fender flares.
Disney Club Penguin 2'' Mix 'N Match Figure Pack - Court Jester and KingIncludes throneUnlock Items Online with special coin2'' HPlasticAges 5+ImportedCollect all the 2'' Figure Packs for more Mix 'N Match fun, sold separately
If you're new here, you may want to subscribe to the mailing list to get notifications of new post and a virtual tour of past topics. Thanks for visiting!









