Data Intergrity question
I have a design question and I'm sure this is a very common one and would like to know how the smart guy deal w/ it. Let's say I have 2 tables:
1. [Order] Table contains fields:
OrderID, OrderName, SalesPersonID, etc
2. [SalesPerson] Table contains fields:
SalesPersonID, SPName, etc
From above [Order] has a reference to [SalesPerson] by SalesPersonID and I didn't enforce the foreign key constraint since Sales Peson comes and goes frequently and deleting a Sales Person doesn't meat to delete an Order.
Now my dilema is if I delete a Sales Person record then the Order that referenced to the deleted Sales Person will lose his information when displaying on the application.
If I store the Sales Person information (let's say SPName) into the Order Table then it creates the classic update change issue that SalesPerson information change won't be updated to the Order Table.
From what I'm doing I simply don't delete the [SalesPerson] record at all that I add a "DelFlag" field to the [SalesPerson] Table and when a Sales Person is deleted from my application the Data Row is not actually removed from the Table but simply mark as deleted as "DelFlag" = 1.
But in this case I kept a lot of junk [SalesPerson] records and
I would like to know how others are dealing w/ this common sceneriao.
Normally, I also use the same way. If the sale person who is wanted to delete has any order related with him, I won't delete this person actually, I just change the status of sale person...
The important thing is that its' depend on what kinda information of sale persons you want to have as the history and what kinda search features you want to provide to your application and what kinda of reports you like to have..
If you don't need that much info abt sale persons, you can think of other way....
Last Post: 04-23-2005, 03:45 PM
By Kenhow in forum VB Classic
Last Post: 03-29-2002, 09:53 PM
Last Post: 03-13-2002, 11:59 AM
By shannan in forum VB Classic
Last Post: 05-04-2001, 04:02 PM
By Tim Frost in forum xml.announcements
Last Post: 04-02-2001, 10:53 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL