Duplicate data for the Key Field

[bpifer23]

I have ran into a snag with a database key field. the key field is case number. The problem that is occuring is once the information is received for the case number the item is given a completed date. Now what can happen (not often but it does happen) is the same case number comes through the system again to receive new information about the case number that is already given a completed date. Now the first completed date needs to stay there for data tracking. Is there anyway around this problem, any suggestions would be great.

Thanks,
Brian

 

[willir]

Brian

This is tough issue in the way the process works. An easy way to address this is create "your" unique primary key. Use your unique ID for your relationships, linking type of thing.

And keep using your case number.

This is similar to defining a G/L accounting system. Often, a developer will use the G/L account for the primary key for tables and links. But when the accountant wants to rename the G/L chart, then the entire transaction file, balance file, etc have to be re-numbered.

A simpler solution is to use the G/L account in an accounting system, and the case number in your system as a descriptive field, and not as the primary key. This way, you are database is independant of the case number.

Richard

 

[Craig0201]

Brian,

How far down the line with this app are you?

To me, it sounds like you could do with altering the data structure. The case could be split into case and actions. Case number would be the PK in case and a foreign key in actions. Each action would have a case and an action type. Each case could therefore be opened, closed, reopened, reclosed ad infinitum via actions.

Craig

 

[willir]

Craig - Actions = History. Great idea.

The nice thing about using the history file is now the case worker will be aware of previous work, and can review the past information.

...Unless the re-used Case number is different people or totally different situation. Then we are back to the original problem where the developer can not control the uniqueness of the case number.