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

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.

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

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!

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?

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.

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

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.