Hi:
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, FK_SalesPersonID, etc

2. [SalesPerson] Table contains fields:
SalesPersonID, SPName, etc

From above [Order] has a foreign key to [SalesPerson]. What happen if an Order's referencing Sales Peson is deleted?

From what I'm doing 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 I would like to know how others are dealing w/ this common sceneriao.

Much appreciated

Since you don't want to delete the order, and assuming you want to keep the information of which salesperson got the order, you can't delete the salesperson row. So a "logical delete" flag like your DelFlag column is a common approach. Other (very similar) approaches are an IsCurrent column ('Y' or 'N') or a Status column with approproate values.

If you didn't want to know who the salesperson was any more you could set the FK col to null and then delete the salesperson. But usually you don't want to do that.

Thank Tony for reply.

Using a logical delete flag is ok but in this case I would be keeping a lot of junk data.

Worst, I have a lot of this kind of relationship in my application's tables and all these are getting big that impact performance.

Any idea?

Many thanks.,

What DBMS are you using? A good one should be able to handle big tables without performance problems - assuming you have correctly indexed the tables, gathered optimizer statistics (if DBMS requires them) etc.

You really have only 3 choices here:

1) Flag the non-current rows as you are currently doing, but keep them.

2) Delete the non-current rows AND the orders that relate to them. Maybe archive the orders somehow first.

3) Delete the non-current rows, and set the salesperson_id to NULL on the related orders.

99% of the time, option (1) is the best because no information is lost or made inaccessible to users.