Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Updating

Author  Topic 

Microsovt
Starting Member

7 Posts
Posted - 2006-03-08 : 07:17:37
Hi All,I'm a new member here. And Sorry cause this subject is not about SQL Server but MySQL. Still, it's related to database design so i'm hoping you all can suggest me.

My question is about Foreign Key and data updating.

I have tables :

1. Items
2. Invoice
3. Supplier
4. Invoice_Items

That records all the company buying history.
The records as follows :

mysql> select * from invoice;
+-----+------------+-------------+
| Id | Date | Id_Supplier |
+-----+------------+-------------+
| 100 | 2004-11-10 | 1 |
+-----+------------+-------------+

mysql> select * from items;
+----+---------+
| Id | Name |
+----+---------+
| 1 | Goods S |
| 2 | Goods B |
| 3 | Goods C |
+----+---------+

mysql> select * from supplier;
+----+------------------+
| Id | name |
+----+------------------+
| 1 | Orackel |
| 2 | Microsovt |
| 3 | Compaqks |
| 4 | San MicroSystems |
+----+------------------+

mysql> select invoice_items.Id_invoice,invoice.date as 'Date Of Transaction',supplier.name as 'Supplier Name',items.name
as 'Item Name',invoice_items.Qty
-> from invoice, supplier, items, invoice_items where
-> invoice.id = invoice_items.id_invoice and invoice_items.id_items = items.id
-> and invoice.id_supplier = supplier.id;
+------------+---------------------+---------------+-----------+------+
| Id_invoice | Date Of Transaction | Supplier Name | Item Name | Qty |
+------------+---------------------+---------------+-----------+------+
| 100 | 2004-11-10 | Orackel | Goods S | 23 |
| 100 | 2004-11-10 | Orackel | Goods B | 21 |
| 100 | 2004-11-10 | Orackel | Goods C | 2 |
+------------+---------------------+---------------+-----------+------+

Now, the last query i made depict transaction with supplier name Orackel.
Suppose someday later, i realize that i have record wrong supplier name, instead of Orackel, it should "Oravel". To fix it, i change the supplier name :

mysql> update supplier set name = 'Oravel' where id = 1;

Then the transaction is also change! (which i don't want this to happen). But, i need to change the Supplier name.


mysql> select invoice_items.Id_invoice,invoice.date as 'Date Of Transaction',supplier.name as 'Supplier Name',items.name
as 'Item Name',invoice_items.Qty
-> from invoice, supplier, items, invoice_items where
-> invoice.id = invoice_items.id_invoice and invoice_items.id_items = items.id
-> and invoice.id_supplier = supplier.id;
+------------+---------------------+---------------+-----------+------+
| Id_invoice | Date Of Transaction | Supplier Name | Item Name | Qty |
+------------+---------------------+---------------+-----------+------+
| 100 | 2004-11-10 | Oravel | Goods S | 23 |
| 100 | 2004-11-10 | Oravel | Goods B | 21 |
| 100 | 2004-11-10 | Oravel | Goods C | 2 |
+------------+---------------------+---------------+-----------+------+

What should i do to solve the problem?

jhermiz

3564 Posts
Posted - 2006-03-08 : 08:23:51
What in the hell are you talking about ?


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

Tahsin
Starting Member

34 Posts
Posted - 2006-03-08 : 10:16:53
This sounds like a college assignment. At any rate, what do you mean by "Then the transaction is also change!" ??? From what I vaguely understand, I think you need to put some joins in your update statement.
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

292 Posts
Posted - 2006-03-08 : 11:10:03
I agree with Tahsin, sounds like coursework.

You need to think about 2 things:
1. What did you do when you ran your update statement?
2. Carefully review your select statement.
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2006-03-08 : 11:50:18
By the way this is a forum primarily for Microsoft SQL Server, it looks like you are using mysql and you could get more specific help from a mysql website.

-------
Moo. :)
Go to Top of Page

Microsovt
Starting Member

7 Posts
Posted - 2006-03-08 : 14:20:49
Thanks all for the answer

No, it's not a coursework or a college assignment or something like that. You may believe or not. If you don't believe then don't answer my question. It's simple, right?

It's just a question. Ok, i know i use mySQL but the question is very common in database design.

I had ask this question on mySQL forum too. And i really appreciate the answer given by one of the member there. He gave me a link to a .pdf file but i kind of don't really understand what the book is saying. So i just ask this in this forum.

I'm just need your opinion. That's all.

What the hell did i ask?

The user that entered the data can make mistake. Suppose you have a Supplier table and also you have a transaction table. You have insert a row into supplier table (suppose id = 1, name = Orackel).
One day you use this supplier data into the transaction table (suppose invoice_number = 10201, date = '2001-11-12', supplier_id = 1).

But, the later day you realize that your user have input wrong supplier name. instead of Orackel, it should Oravel.
If you change the supplier name, the transaction is also change.
First, the invoice_number = 10201 with supplier name is Orackel then
it would became invoice_number = 10201 and supplier name is Oravel.

What should you do? you insert a new supplier with id = 2, name = Oravel and use it in the next transaction?

This example is just about supplier name. As we know supplier also has address and address can change.
sometime, what we want is if we change the parent table then the child table is also change (with Cascade). But sometime, we don't want it to change like the sample i describe above.

The last word, if you don't want to answer my question, then don't answer. It's really simple like that.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-03-08 : 19:23:56
It doesn't sound like you have a problem.

The supplier name was wrong and you changed it to the correct name. The select statement then shows the correct name. That is what I would want and expect.


If you don't like the answers you get, don't post here.






CODO ERGO SUM
Go to Top of Page

Microsovt
Starting Member

7 Posts
Posted - 2006-03-08 : 21:23:36
Thank you Michael Valentine Jones for the answer.

Ok, from your answer, i think i have gave wrong example.

I try to give a new example. I have a Invoice_items table which a many to many relationship between Invoice and Items.

Someday, a person buy things (items) from the company. The company record it on Invoice_Items table. And the invoice has been given to the person. Later, the company realize that they have record wrong items name. If the company change the items name then it will reflected on Invoice_items table. But, the Invoice_items table is like a history table that record all the selling items (It shouldn't change).

If someday the person who buy from the company make a complain about the items (maybe it's broken or something like that), The company will use the Invoice number to understand what the person had bought. But, the items name has change, it won't reflect the exact items had been bought.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2006-03-08 : 21:33:31
Currently your invoice_items table does not have the item name there. If you need the history of the item name, create another column item_name in your invoice_items table and populate it during creation of invoice. And your select statement change items.name to invoice_items.item_name

select invoice_items.Id_invoice,invoice.date as 'Date Of Transaction',supplier.name as 'Supplier Name', invoice_items.item_name
as 'Item Name', invoice_items.Qty
from invoice, supplier, items, invoice_items where
invoice.id = invoice_items.id_invoice and invoice_items.id_items = items.id
and invoice.id_supplier = supplier.id


----------------------------------
'KH'
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-03-08 : 21:48:49
quote:
Originally posted by Microsovt

Thank you Michael Valentine Jones for the answer.

Ok, from your answer, i think i have gave wrong example.

I try to give a new example. I have a Invoice_items table which a many to many relationship between Invoice and Items.

Someday, a person buy things (items) from the company. The company record it on Invoice_Items table. And the invoice has been given to the person. Later, the company realize that they have record wrong items name. If the company change the items name then it will reflected on Invoice_items table. But, the Invoice_items table is like a history table that record all the selling items (It shouldn't change).

If someday the person who buy from the company make a complain about the items (maybe it's broken or something like that), The company will use the Invoice number to understand what the person had bought. But, the items name has change, it won't reflect the exact items had been bought.



In that case, do not allow changes to the Items table. Make it so that you can only add new entries.

You may have to make some other changes, like adding an ACTIVE indicator to show that the item is no longer used, date it was deactivated, etc.

You might also do this by giving your Items table a version number, and make the combination of Item number and version number the primary key. Then the Invoice_Items table would relate to the Items table via Item number and version number. There would still be a new entry in the Items table for each version of the item.





CODO ERGO SUM
Go to Top of Page

Microsovt
Starting Member

7 Posts
Posted - 2006-03-09 : 00:49:35
Thanks to khtan and Michael Valentine Jones for your answers :D
I really appreciate your opinions.

To khtan :
Yes, it seems like a solution. But, is it not a normalize table?
Although, i must admit that i have use your suggestion in my previous application a few years ago, he..he..he.

To Michael :
Yes, it also seems like a nice solution. Using version number and a flag with boolean values. Nice, :D
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts
Posted - 2006-03-09 : 00:59:34
i may oversimplify this (which i always do )...

if let' say your parent data changes and you have child transactions already...

changing the value like name can be cascaded down, because you will have a reference number and that number will not change... unless you want to reflect and query the previous value...
in that case, you may need to employ a 'posting' table, that will house your transactions that have not been actually 'committed' and waiting for approval or verification

once approved/verified, then you post those transactions as committed

or

you can have a separate table for updates/deletes, more of an audit trail for these type of changes/mistakes... hopefully they're really not that many

HTH

--------------------
keeping it simple...
Go to Top of Page

twhelan1
Yak Posting Veteran

71 Posts
Posted - 2006-03-09 : 09:43:19
Microsovt,
It's not uncommon for historical tables to be denormalized. Lets change your example from item name to item price. If you change the item price, you wouldn't want that to be reflected on past orders because it would make your accounting wrong. The solution to that problem is to store the item price at the time of the sale in the invoice table.

The other solution, as Michael suggested is versioning. You would have to have an item version, a customer version, etc. though to be able to use a perfect 3NF design and still maintain customer and item history on your invoices.

I think it's generally standard that invoice and order tables have some denormalized data to maintain historical information like price and shipping address.

~Travis
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-03-09 : 09:54:35
You are confusing thigns by saying "you have the wrong customer *name*" or the "wrong item *name*". Do you mean the wrong CUSTOMER or the wrong ITEM ? big, big difference. You should not be storing names with transactions.

If transactions are booked in the wrong place (i.e., to the wrong customer or with the wrong Item), the cleanest thing to do is to reverse them out with negatives and rebook them in the right place. This way things zero out properly and you have an audit trail. You can add "transaction types" or flags or something to indicate that these entries were corrected in this manner if you wish to exclude them from certain reports. Accountants do this all the time. It's really the best of both worlds -- easy to fix, the data is now correct, you have an audit trail, etc.

If it is a simple matter of data entry being wrong for something that is just a label, i.e., a customer name, then fix it in the customer table.

If you need to maintain a history of cutomer name changes over time, then you need to create a new table, pk of (CustomerID/Date) and store customer info that changes historically.
Go to Top of Page

Microsovt
Starting Member

7 Posts
Posted - 2006-03-09 : 22:08:28
Thank you to jen, twhelan1, and jsmith8858 for all your answers



quote:
Originally posted by twhelan1

It's not uncommon for historical tables to be denormalized. Lets change your example from item name to item price. If you change the item price, you wouldn't want that to be reflected on past orders because it would make your accounting wrong. The solution to that problem is to store the item price at the time of the sale in the invoice table.


Yes, i get your point, twhelan1.

quote:
Originally posted by jsmith8858

If transactions are booked in the wrong place (i.e., to the wrong customer or with the wrong Item), the cleanest thing to do is to reverse them out with negatives and rebook them in the right place.


Can you explain what do you mean by "reverse them out with negatives" ?

From this forum, i got many input.
I think this is the best to do regarding my question :


  • For Supplier name that changed, i create a new supplier_id and enter new data for the supplier. Also, i use a flag that indicate that previous supplier is deactivated. I also can use versioning or date like Michael and jsmith8858 suggested. This is also applicable for items table or other "Basic" tables.

  • For data that shouldn't change like prices and shipping address (as twhelan1 explained), i will record it on transaction table.



Thanks all for your opinions. I really appreciate it.

Although that, i still welcoming another comments
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-03-10 : 09:17:28
>>For data that shouldn't change like prices and shipping address (as twhelan1 explained), i will record it on transaction table.

No! Why would you do this? Why store redundant information in transaction tables, especially when you just said it shouldn't change.

When I spoke about "reversing out" transactions, I meant that if you accidentally enter the wrong Customer or Product for an order:

OrderID, LineItem, Customer, Product, Amount
1234,1,ABC,Widgets,10

Then to correct it, all you need to do is reverse out the error and re-book the transaction to the correct place:

OrderID, Customer, Product, Amount
1234,1,ABC,Widgets,10
1234,2,ABC,Widgets,-10
1234,3,DEF,Widgets,10

Again, the issue is you have mentioned several situations in which data entry is wrong, and you are confusing the issues. You need to very clearly think about the differnet situations.

Situation #1: a TRANSACTION is entered improperly
Resolution: Create transactions to reverse out errors, then create the proper transactions. Use flags or "transactionType" codes as necessary to clearly highlight or suppress mistakes as necessary.

Situation #2: Address information, a customer name, salesperson name, etc is incorrect -- and there is no need to store historical records of these changes
Resolution: Only store the data in the proper place (i.e, normalized) and simply fix it. Done.

Situation #3: You need to keep track of customer name changes , or sales rate or price changes for products, or other attributes of an entity that change over time
Resolution: then you need to create history tables for these attributes and record changes there. Those attributes then do NOT belong in transactions OR in the master entity tables.

There are others, but those are the 3 basic scenerios. You need to clearly define, ahead of time, what you need to track and how different situations should be "fixed".

Go to Top of Page

Microsovt
Starting Member

7 Posts
Posted - 2006-03-17 : 05:39:25
Thanks, jsmith8858 for your comments and sorry for "late" reply.

Ok, now i understand what you mean by "Reversing out"

I also understand what you mean in situation #1, #2 and #3.

Yes, i think your suggestion is Good solution. I never think about this before.

quote:

There are others, but those are the 3 basic scenerios. You need to clearly define, ahead of time, what you need to track and how different situations should be "fixed".


Are these "scenarios" you got from experience or from a book?
if by the book, can you tell me what is the title of the book? so i can get more reference

Ok, this is my conclusion from what i get from your suggestion spesifically from situation #3.

If i have a basic table (suppose it's "items" table) and i need to record the price changes for an item then i will make a new table called "item_price" which have fields "price" and a foreign key item_id from "items" table. The primary key for this table ("item_price") are item_id and date_recorded.

Thank you
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site