When I use the same lookup table for two attributes in the same main table each attribute is kept in synchronisaion. How do I set up loookup tables so that this doesn't happen?
Example: LookupZIP has the values A B & C
My main table has attributes:
Name Bill ZIP Job ZIP
Both BillZIP & JobZIP should use LookupZIP but if I change JobZIP to C then BillZIP also changes to C
I am using a SQL Server database with VB2008.
I would say that this is a design issue. If you are using LookupZIP as a common lookup table, you have to realize that C is C where ever it exists, and if you change the value C to something else then it should be changed everywhere. That is the basic foundation of relational databases. Every table that is using the lookup table has a relation to the data. The data should somehow reflect the reality.
If the values should be different depending on where you use the lookup value, you have to add a column to the key of the lookup table and use that column in your referenced tables, or use a new lookup table. You could use a "type" column and change your data to reflect the differnce between the data. Then you could use multiple C depending on how you want to use it.
BillType C JobType C
If you do not want the data to be updated anywhere when you try to update your lookup table, you have to make sure that "CASCADE UPDATE and CASCADE DELETE" of the Foreign Key relationship is set to false. You also have to make sure that there is no trigger that is fired and updates the data.
Are you changing the value of in the table with BillZip & JobZip or are you changing the value in the LookupZip table? If you're changing the value in the LookupZip table, then you should expect that value to change everywhere it's being referenced. Although, that raises another question, are you changing the lookup value or the lookup key? In general, you should not be changing key values.
But, if you're saying that you have two different columns in a table and that when you update one column, like this:
UPDATE MyTable
SET JobZip = 'C'
That the value changes in the column BillZip as well... something else is going on. In that case BillZip is a calculated column or there's a trigger... you'll need to check your structure out more thoroughly for us to understand what the problem might be.
