I have a table to keep record of changes in certain elements (the table is called "Changes"). The stucture is like this:
elementId: Number (linked to the primary key in table "Elements")
changeTypeId: Number (linked to the primary key in table "ChangeTypes")
The "Elements" table is like:
elementTypeId: Number linked to the primary key in table "ElementTypes")
I can notice there's some redundancy in the data, since looking at the changes in table "Changes", I can infer the type of the element subject of the change from two columns. For example, a change involving element 342 (which is type A) has a changetype 23 (which only aply to elements of kind A). So looking at either of those fields, I can tell the change refers to a type A element.
To make things cleares, the ChangeTypes table contains several groups of typechanges, each group being applicable only to one type of element. I'm "enforcing" this using a combo to show only the values valid to the type of element selecte in the above field.
The thing is I know there's nothing in the structure to prevent the potential risk of getting inconsistent data (element 34, which is type C, suffering a change 39, whic only applies to type B).
How can I further split my tables to get rid of this problem ?