Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 9th, 2008, 03:33 AM
Authorized User
 
Join Date: May 2008
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Can I have a constraint like this.....?

Hi,

Is it possible to have a SQL constraint (I am on mysql at the moment) for the following?

Background: Table has a parent_id column that allow a row in the table to reference another row in the table. The table list bank transactions, but allow the ability to say row A is actually made up of two separate items (which you enter manually) in row B & C. For example:

Row 1 - $100 (bank transaction)
Row 2 - $20 (item X), parent_id=1
Row 3 - $80 (item Y), parent_id=1

Q - Can I create a constraint that requires, for a successful save, that SUM(parent) = SUM(children).

That is this would make sure the table data is always consistent. It would not be possible to allocate Row 2 as child and forget to add Row 3 as remaining child, as they wouldn't add up.

Is this possible? Or is there a way to protect against this somehow?

Thanks
Reply With Quote
  #2 (permalink)  
Old August 11th, 2008, 11:55 PM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Creating an identifier to reference a row within the same table is not advisable. You may face conflicting results and data integrity problem later. From what i gathered from your info, you are trying to reconcile a data summary to its details. Why not creating a table to store the data, in details, and perform a select query to derive the summary result. That way, you would not need to worry about whether the data in summary agrees to its details. You may create a separate table specifically for banker's information, and link the transaction details to the transaction table.

Reply With Quote
  #3 (permalink)  
Old August 12th, 2008, 03:12 AM
Authorized User
 
Join Date: May 2008
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I see your point, however I wondering how best to take this advice. Basically the initial data is from a bank account transaction statement being imported, however what I really am after from the business logic perspective is to see a list of items (call them business items) that reflect a real world item. There are several scenarios however:

(a) bank item = business item (most common case). That is you buy an item and pay for it in the same transaction.

(b) bank item = covers several business items (e.g. you purchased several things from the shop, however only one credit card statement)

(c) multiple bank items = 1 x business item (e.g. the other day I had to pay several thousand and scheduled the payment over 3 days to fit in with the maximum allowed limit for bank transfers per day).

So I want to assume the starting point is the bank transactions I import (as this covers 90% in a 1 bank item = 1 business item) but then when there is a variation to this (i.e. cases b and c above) I then take manual action to break them out. For example select the bank item and say "this is actually made up of two separate business items", where the sum of the business items is equal to the bank transaction. So I thought keeping everything in the same table might be easier.

Make sense? What are your thoughts? To break business items and bank items into their own tables would require duplication 90% of the time...

Thanks
Reply With Quote
  #4 (permalink)  
Old August 12th, 2008, 11:49 PM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi callagga,

You mentioned that, the bank statement is imported into your database. Does the transaction items appears on the statement, contains all the details you want? If the imported data requires additional input, let say, you have more than 1 bank and you need to tag a description to each transaction line items, I suggest you create a table to store the bank's name, address, contact, Bank_ID(PK).Bank_ID would be the table's identifier, assigned to each bank. Create a separate table to store transaction details, e.g. purchase_amount,payment_amount,description,payment _type,Bank_ID (FK).Payment_type allows you to categorise your payment into, cash or credit card.These two table will be consistently linked via Bank_ID column, created as the referencing field.

To sum a transaction amount for a particular bank,by payment type, just apply the filtering condition in your select query.

Hope that helps!



Reply With Quote
  #5 (permalink)  
Old August 13th, 2008, 12:05 AM
Authorized User
 
Join Date: May 2008
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

that makes sense, however it's not the scenario I was referring to. I'm talking about a situation where you might start with 8 transactions say (call these bank items), into the TRANSACTIONS table:

1 Hardware
2 Fence Company
3 Pool Company
4 Food Shop
5 Real Estate Firm $10
6 Real Estate Firm $10
7 Real Estate Firm $5
8 Hardware Shop $100

But the last 5 items should really be represented as follows in the application:
* Real Estate Purchase at Penny Lane - $25
* Parts for Rental Property (from hardware) - $60
* Parts for Own House (from hardware) - $40

So this is where I was thinking of adding these as new items to TRANSACTIONS, having the old items refer to these new ones as a Parent item (i.e. with a Parent ID that points back to the TRANSACTIONS table), and a flag that indicates it is now only a child.

Make any sense?
Reply With Quote
  #6 (permalink)  
Old August 13th, 2008, 12:46 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

Dear callagga,
I would like to tell you how I handled this case

1) First you are entering parent and child in same table please avoid this

2) I prefer 2 separate tables one parent table and another child table

3) Build transaction and commit if whole sequence executed successfully

   Transaction sequences is as follows..

   Begin transaction
   a) Insert one record in parent table generate one transaction id
   b) If above statement succeed then insert child records with new trans id
   c) If above statement succeed then sum amount in child table and update parent table with that amount using new trans id
   d) If above statement succeed then COMMIT else ROLLBACK

Now row level constraints are generally handled in trigger, if your rule fails then you can raise error and stop sql execution

MYSQL HAS NO FACILITY TO RAISE ERROR YET. YOU HAVE TO USE TRANSACTION FOR DATABASE INTEGRITY

urt

Help yourself by helping someone.
Reply With Quote
  #7 (permalink)  
Old August 13th, 2008, 12:59 AM
Authorized User
 
Join Date: May 2008
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So then to get a complete view of my transactions I would need to almost make a UNION of the (parent records) & the (child records for which there are no parent records, or child records which are active & have not been superseeded by a parent record) then. Is this the concept?

E.g For my scenario the union of these would then give:
1 Hardware
2 Fence Company
3 Pool Company
4 Food Shop
10 Real Estate Purchase at Penny Lane - $25
11 Parts for Rental Property (from hardware) - $60
12 Parts for Own House (from hardware) - $40

Child items 4-8 would have been marked inactive.

By the way, why is it so bad to have a reference to another record in the same table?

Thanks
Reply With Quote
  #8 (permalink)  
Old August 13th, 2008, 01:08 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

1) You dont have to user UNION you can use JOIN feature of SQL.
2) And for things which are not active any more you can use status flag in master table
3) In my view It is not bad to refer record of same table, but it should be done only when there is no option,
One case is employee table with manager id

employee id, name, address, city, manager id(Refers to employee id of same table)

Like that. It is your choice what you want to do.

urt

Help yourself by helping someone.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Constraint Vs Index carumuga SQL Server 2000 2 December 4th, 2007 02:14 AM
Identity Constraint in DataSet anujrathi Pro VB.NET 2002/2003 1 June 21st, 2006 04:50 AM
Security constraint for Tomcat anntosh BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 0 February 9th, 2006 04:40 PM
constraint for 1-to-n relation j_yan MySQL 0 December 10th, 2004 11:03 PM
Constraint for names barkingstars Oracle 2 April 14th, 2004 01:29 PM



All times are GMT -4. The time now is 04:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.