Allowing SQL relationships to be broken?

[zachattack05]

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?

[Momerath]

First problem: Add a boolean field to the item table to indicate if the item is discontinued and adjust your SQL code to allow for it.

Second problem: Price should be in it's own table with FK to item table. It should have item #, start date, end date, and price.

[zachattack05]

Momerath,

I considered the first solution. But isn't that a bad design? I mean, over the course of many years the size of the table would be ridiculous with old and outdated information. Wouldn't the performance of the system drastically suffer?

On the second solution. Good idea. :)

[Momerath]

I considered the first solution. But isn't that a bad design? I mean, over the course of many years the size of the table would be ridiculous with old and outdated information. Wouldn't the performance of the system drastically suffer?

Depends :) Normally you'd start to archive data at some point (because of the problems you mention, and others). For example you might keep 1 year of sales in the normal database, 5 years in the archive and everything else dumped to tape (in case you needed it for some reason).

Determining when to archive, what to archive and if any data consolidation can take place (you might decide that after 5 years you don't care who you sold to or what, just how much money per day/week/month you sold) can be as complicated as the rest of your application :)

[zachattack05]

Good point!

Thanks as usual! :)

[jbrock31]

I know you marked this as solved, but you could create a "discontinued table" and move items to it. This will allow for archiving, as well as keeping the "main" table at a smaller size.

[zachattack05]

jbrock31,

Hmmm...that's also a great idea!

The situation I have (the real situation) is thousands of medical patients, hundreds of doctors, nurses, PAs, and laboratory test codes, the substances tested in each code, test results for each substance, account numbers, chain/requisition numbers etc...it's a mess of relationships, but the problem I have is, medical records must be kept for x amount of time (5 years for most here) and that's fine, but that also means if a laboratory removes a test code (because they stop offering it) or if a doctor, nurse etc... quits, gets fired whatever....I can't delete the doctor, the test code etc...because a result is attached to them.

I think I'll need to just do the "soft delete" and have a routine that runs every month or so and checks result dates, deletes (or archives them out of the database) the older ones and then tries to remove the doctors, etc...that are marked with that soft delete flag.

I dunno...it's the best solution I can come up with right now.

EDIT: Oh, and I love the avatar btw :)

[jbrock31]

Haha Thanks my friend. I understand and good luck! ;)