I am rebuilding an Alpha database for a non profit that provides funds to residents of a county. If someone needs rent money, to pay the heat bill, or pay the electric bill they come to this office.
Currently all info about the visit is in one table! So there can be a limited number of family members, etc, and you have to change data ( like last name changes ) and there is no record of the previous information. They want to keep ALL information.
What i have so far is:
General table Name, SSN, clientID
Family table Name, Relation, clientID
Address table City, State, clientID
Visit table Need, Notes, clientID
(please note, i've not listed EVERY field)
So everything links back to the Primary Key General.clientID. I can have as many rows in Family, Address, and Visit as i want and link them to one applicant in General.
I set up everything that way before i realized that the client wants to be able to keep everything. If John Smith comes in and changes his name to John Doe they want to be able to know that he was smith and doe. If he comes in with a different SSN they want to keep both.
All I've been able to think of is creating a Control table with controlID and clientID and making General.clientID not a Key so that i can have something like this:
CONTROL
CID | clientID
001 | 1
001 | 2
GENERAL
First | Last | CID | clientID
John | Doe | 001 | 1
John | Smith | 001 | 2
I'm sure someone here can come up with a better way. It gets kinda hairy going down to visits, family, etc...
Currently all info about the visit is in one table! So there can be a limited number of family members, etc, and you have to change data ( like last name changes ) and there is no record of the previous information. They want to keep ALL information.
What i have so far is:
General table Name, SSN, clientID
Family table Name, Relation, clientID
Address table City, State, clientID
Visit table Need, Notes, clientID
(please note, i've not listed EVERY field)
So everything links back to the Primary Key General.clientID. I can have as many rows in Family, Address, and Visit as i want and link them to one applicant in General.
I set up everything that way before i realized that the client wants to be able to keep everything. If John Smith comes in and changes his name to John Doe they want to be able to know that he was smith and doe. If he comes in with a different SSN they want to keep both.
All I've been able to think of is creating a Control table with controlID and clientID and making General.clientID not a Key so that i can have something like this:
CONTROL
CID | clientID
001 | 1
001 | 2
GENERAL
First | Last | CID | clientID
John | Doe | 001 | 1
John | Smith | 001 | 2
I'm sure someone here can come up with a better way. It gets kinda hairy going down to visits, family, etc...
Comment