I just had a db droppped in my lap and I need to make modifications to it. This db basically tracks addresses that had a certificate issued. This certificate is only good for a year but unfortunately there was no thought put in for records that were to be renewed!
The address table has a 1 to many relationship to the application table and the owner and clean tables have a 1 to 1 relationship to the application table. When a application is renewed all the owner and clean information is just duplicated and a new record (in each of their tables) is added. This is very inefficient! I can fix this but (finally) my question is what is the best way to track renewed applications. I waas thinking of adding a new table tblRenewal and relating it (1 to 1) with the tblApp but I run into the problem of associating the tblOwner and tblClean records the the tblRenewal.
Confused yet?! I know I am!!!!
Here is the db structure:
tblAddress tblApp tblOwner tblClean
AddID AppID OwnID CleanName
StNo AppDate OwnAddress CleanPhone
Street AddID OwnPhone AppID
Suffix AppID
The address table has a 1 to many relationship to the application table and the owner and clean tables have a 1 to 1 relationship to the application table. When a application is renewed all the owner and clean information is just duplicated and a new record (in each of their tables) is added. This is very inefficient! I can fix this but (finally) my question is what is the best way to track renewed applications. I waas thinking of adding a new table tblRenewal and relating it (1 to 1) with the tblApp but I run into the problem of associating the tblOwner and tblClean records the the tblRenewal.
Confused yet?! I know I am!!!!
Here is the db structure:
tblAddress tblApp tblOwner tblClean
AddID AppID OwnID CleanName
StNo AppDate OwnAddress CleanPhone
Street AddID OwnPhone AppID
Suffix AppID
