Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Delete record from One side only of One-Many Relationship
- Thread starter gtroiano
- Start date
Jerry - haven't tested it lately but don't think so. The basis of referential integrity is to protect data relatinonships from being broken, and if you loose the "key" or "parent" field, the "child" becomes parentless; at any rate, what would be your logic behind this? Perhaps there is something else you might want to consider.
- Thread starter
- #3
thanks for the reply.
perhaps there is something else i could do to achieve the same goal but, alas, i can think of anything. the database is for financial accounts. Table 1(One Side) has all the data for each account and Table 2(Many Side) is all the "Budget Codes". now, each account has a Buget Code assigned to it. the purpose of deleting just from the Budget Code Table (Many Side) is this. Occasionally, Budget Codes are added, changed, or deleted. Consequently, there has to be a way to modify the budget code table without actually deleting the account that it is assigned to. if anyone knows a way to do this, i would love to hear it. thanks!
jerry.
perhaps there is something else i could do to achieve the same goal but, alas, i can think of anything. the database is for financial accounts. Table 1(One Side) has all the data for each account and Table 2(Many Side) is all the "Budget Codes". now, each account has a Buget Code assigned to it. the purpose of deleting just from the Budget Code Table (Many Side) is this. Occasionally, Budget Codes are added, changed, or deleted. Consequently, there has to be a way to modify the budget code table without actually deleting the account that it is assigned to. if anyone knows a way to do this, i would love to hear it. thanks!
jerry.
- Thread starter
- #5
zambezibill
Technical User
Now seems a good time to interject a blurb on primary key fields.
1. Primary key fields should never (no exceptions) be data derived or related. They should be a preferably numeric value used only to uniquely identify the record and provide a basis for relationships with other tables.
It sounds as if the Budget Codes were used as the primary key field and hence the field upon which relationships to other tables was based. Since the Budget Codes are data, and data invariably gets changed, you are backed into a corner.
The fix is tedious and cumbersome. Add a new primary key field to the Budget Code table. I suggest setting it as an autonumber for convenience. Then add this field to other tables as a foreign key. Use update queries to update the foreign key fields with the related primary key number. Then when you have all of the new relationships correctly established, you can delete the old relationships between involving the Budget Codes and make the necessary data changes.
Much easier to have spent the extra 5 minutes to do it correctly up front. Of course I learned the lesson in the real world by making the same mistake.
Caveat: back up the tables first.
Cheers, Bill
1. Primary key fields should never (no exceptions) be data derived or related. They should be a preferably numeric value used only to uniquely identify the record and provide a basis for relationships with other tables.
It sounds as if the Budget Codes were used as the primary key field and hence the field upon which relationships to other tables was based. Since the Budget Codes are data, and data invariably gets changed, you are backed into a corner.
The fix is tedious and cumbersome. Add a new primary key field to the Budget Code table. I suggest setting it as an autonumber for convenience. Then add this field to other tables as a foreign key. Use update queries to update the foreign key fields with the related primary key number. Then when you have all of the new relationships correctly established, you can delete the old relationships between involving the Budget Codes and make the necessary data changes.
Much easier to have spent the extra 5 minutes to do it correctly up front. Of course I learned the lesson in the real world by making the same mistake.
Caveat: back up the tables first.
Cheers, Bill
zambezibill
Technical User
Now seems a good time to interject a blurb on primary key fields.
1. Primary key fields should never (no exceptions) be data derived or related. They should be a preferably numeric value used only to uniquely identify the record and provide a basis for relationships with other tables.
It sounds as if the Budget Codes were used as the primary key field and hence the field upon which relationships to other tables was based. Since the Budget Codes are data, and data invariably gets changed, you are backed into a corner.
The fix is tedious and cumbersome. Add a new primary key field to the Budget Code table. I suggest setting it as an autonumber for convenience. Then add this field to other tables as a foreign key. Use update queries to update the foreign key fields with the related primary key number. Then when you have all of the new relationships correctly established, you can delete the old relationships between involving the Budget Codes and make the necessary data changes.
Much easier to have spent the extra 5 minutes to do it correctly up front. Of course I learned the lesson in the real world by making the same mistake.
Caveat: back up the tables first.
Cheers, Bill
1. Primary key fields should never (no exceptions) be data derived or related. They should be a preferably numeric value used only to uniquely identify the record and provide a basis for relationships with other tables.
It sounds as if the Budget Codes were used as the primary key field and hence the field upon which relationships to other tables was based. Since the Budget Codes are data, and data invariably gets changed, you are backed into a corner.
The fix is tedious and cumbersome. Add a new primary key field to the Budget Code table. I suggest setting it as an autonumber for convenience. Then add this field to other tables as a foreign key. Use update queries to update the foreign key fields with the related primary key number. Then when you have all of the new relationships correctly established, you can delete the old relationships between involving the Budget Codes and make the necessary data changes.
Much easier to have spent the extra 5 minutes to do it correctly up front. Of course I learned the lesson in the real world by making the same mistake.
Caveat: back up the tables first.
Cheers, Bill
- Thread starter
- #8
- Thread starter
- #9
Just a thought, but are the Budget Codes ever reused???? Here is something I was thinking of....don't know if it will work, but you can think about it...
Try using an intermediary table....one just to store budget codes.....the table would have no other relevance....when you change or add budget codes, just check to make sure that the code is correct in this intermediate table. When you delete codes, you don't have to worry about it....
Then set your relationship to this table instead of the primary budget code table...kind of like a many-to-many step table.....
Currently you have:
Budget Table -1-----Many> Account Table
Change to:
Budget Table <Many---1- CodeHoldingTable -1---Many> AccountTable
Really not sure if this will work for you.....but hey, you never know...
Don't forget to backup your database prior to making any changes and trials...Good Luck! "As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein![[spin]](/data/assets/smilies/spin.gif "[spin] [spin]")
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
Try using an intermediary table....one just to store budget codes.....the table would have no other relevance....when you change or add budget codes, just check to make sure that the code is correct in this intermediate table. When you delete codes, you don't have to worry about it....
Then set your relationship to this table instead of the primary budget code table...kind of like a many-to-many step table.....
Currently you have:
Budget Table -1-----Many> Account Table
Change to:
Budget Table <Many---1- CodeHoldingTable -1---Many> AccountTable
Really not sure if this will work for you.....but hey, you never know...
Don't forget to backup your database prior to making any changes and trials...Good Luck! "As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
- Thread starter
- #11
Yes, that is what I meant.....For example, let's say the budget codes are three digit entries
If BLY = somecode and you delete/change BLY this month, will BLY ever be used agin in the future as someothercode...
Or better explained:
Yesterday you had BLY = Billboard Expense Account
Today you delete this code
Tomorrow, if you use BLY again...Will it HAVE to = Billboard Expense Account or can it = Dog Grooming Expense?
If the code can be reused but never changes, the itermediate table I discussed should work for you...if the code does change, it definitely will not. "As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
If BLY = somecode and you delete/change BLY this month, will BLY ever be used agin in the future as someothercode...
Or better explained:
Yesterday you had BLY = Billboard Expense Account
Today you delete this code
Tomorrow, if you use BLY again...Will it HAVE to = Billboard Expense Account or can it = Dog Grooming Expense?
If the code can be reused but never changes, the itermediate table I discussed should work for you...if the code does change, it definitely will not. "As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein
Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
- Thread starter
- #13
robert,
thanks for the help. the budget codes probably won't be reused but i cannot say for sure. who knows what these crazy users will do! hence, i have changed things around a little: now, i just have a message box telling the user that deleting the budget code deletes the related accounts also. again, thanks!
jerry.
thanks for the help. the budget codes probably won't be reused but i cannot say for sure. who knows what these crazy users will do! hence, i have changed things around a little: now, i just have a message box telling the user that deleting the budget code deletes the related accounts also. again, thanks!
jerry.
It seems to me that what you want is to keep the historical account data, rather than lose it when it no longer fits the current budget codes. Keeping historical data unchanged when the business rules change is a fairly common problem, and there are a number of possible solutions:
1. You can logically delete budget codes, rather than physically delete them. You do this by setting a yes/no flag in the Budget Codes table that indicates the code is no longer valid for new accounts. One advantage of this method is that, if you use the Budget Codes table as the Row Source for a list or combo box, you can make a slight modification to the Row Source query so that only currently valid codes are listed when adding transactions, yet the "deleted" codes are still present for historical ones. One disadvantage is that you can't easily reuse a logically deleted code without changing the meaning of your historical data, especially if the Budget Codes table has additional fields. But reusing deleted codes is unwise anyway, because of the risk of confusion.
2. You can move historical data to a parallel table that does NOT have a relationship to the Budget Codes table, or only has a non-RI relationship. Typically, when this is done, you want to denormalize the historical records by replacing foreign keys with the columns from the table they refer to. If you do this, you don't need the relationships any more. This protects you from damage to the historical data caused by changes in the active tables. It also makes the historical data more efficient for searching and reporting, since it's all in one table and no joins are needed. Searching and reporting is what you keep historical data for, after all. Finally, if you have a lot of historical data, removing it from your active tables makes them more efficient, too. On the down side, the history table will tend to use up more disk space, since it typically contains a lot of redundant data.
(BTW, if you still need to see all your transactions--active and historical--together, it's not hard. You just have to create a UNION query that merges the history table with the query you were already using. It won't be updatable, but you shouldn't be trying to update the historical data anyway, and you can still use the original query to update the active data.)
3. You can turn off RI on the relationship, which allows "orphaned" account records (child table has no related row in parent table). This is easy to do, but generally it means you'll have to do extra coding to maintain data integrity where you need it, and it can make your queries a lot more complex because of the need to use outer joins when data may be missing from one table of the join. There's also the risk that people who have direct access to tables or queries in the Database Window might be able to damage your current (non-historical) data, when RI is not in effect.
Rick Sprague
1. You can logically delete budget codes, rather than physically delete them. You do this by setting a yes/no flag in the Budget Codes table that indicates the code is no longer valid for new accounts. One advantage of this method is that, if you use the Budget Codes table as the Row Source for a list or combo box, you can make a slight modification to the Row Source query so that only currently valid codes are listed when adding transactions, yet the "deleted" codes are still present for historical ones. One disadvantage is that you can't easily reuse a logically deleted code without changing the meaning of your historical data, especially if the Budget Codes table has additional fields. But reusing deleted codes is unwise anyway, because of the risk of confusion.
2. You can move historical data to a parallel table that does NOT have a relationship to the Budget Codes table, or only has a non-RI relationship. Typically, when this is done, you want to denormalize the historical records by replacing foreign keys with the columns from the table they refer to. If you do this, you don't need the relationships any more. This protects you from damage to the historical data caused by changes in the active tables. It also makes the historical data more efficient for searching and reporting, since it's all in one table and no joins are needed. Searching and reporting is what you keep historical data for, after all. Finally, if you have a lot of historical data, removing it from your active tables makes them more efficient, too. On the down side, the history table will tend to use up more disk space, since it typically contains a lot of redundant data.
(BTW, if you still need to see all your transactions--active and historical--together, it's not hard. You just have to create a UNION query that merges the history table with the query you were already using. It won't be updatable, but you shouldn't be trying to update the historical data anyway, and you can still use the original query to update the active data.)
3. You can turn off RI on the relationship, which allows "orphaned" account records (child table has no related row in parent table). This is easy to do, but generally it means you'll have to do extra coding to maintain data integrity where you need it, and it can make your queries a lot more complex because of the need to use outer joins when data may be missing from one table of the join. There's also the risk that people who have direct access to tables or queries in the Database Window might be able to damage your current (non-historical) data, when RI is not in effect.
Rick Sprague
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question