I know this makes no sense at all, but hear me out and maybe someone can supply me with some info on how this can be accomplished correctly.
Using an invoicing type example.
You have a table of clients who can receive invoices, a table of invoices with invoice numbers and client ids, and a table of invoice details with invoice numbers and items on those invoices and then an items table where details about the items are stored that are shown on invoices.
Now, assuming the system is heavily used and thousands upon thousands of records could be in any of those tables at one time or another, what happens if an item is discontinued?
With a relationship setup like this, deleting the item from the items table won't work, it will delete the item from all the invoices as well. Even if it could be deleted from the items table and you left the info in the invoices tables you have a reference to a key in the items table that doesnt exist. The invoice would refer to item # whatever, but if the item is removed from the items table you have no idea what that item originally was!
So, how can you make sure that any items added to a new invoice contain only available items, and the invoices that have discontinued items on them can still display what the item was, the cost etc?
Is this a situation where you would NEED redundant information? Basically copying the row from the items table and "pasting" it into the invoice items table so that if an item is deleted the data is still there where it needs to be?
Seems redundant, but, I'm not sure what to do.
Same issue applies with price changes, item A is sold for $100 today, but in a month it's $140, I don't want all the old invoices to show the new price, it should show the old one!
Any ideas? Suggestions?