I'm trying to convince the powers-that-be that their current database design is risky and inefficient. I'd like to verify whether my assumptions are correct, and I'd like help in finding technical literature and/or terminology that explains the problem in a way that sounds more convincing than my "It's bad, mmmkay."
It's parcels data. The current structure is as seen here:
PARCEL_ID PARCEL_TYPE RIGHTS_SURFACE RIGHTS_MINERAL 46 EASEMENT 0 0 47 DISPOSED 1 0 47 OWNED 0 1
I see two, related issues with the PARCEL_TYPE column:
1) A single column identifies ownership. However, we may own mineral rights but not surface rights. In which case, the current workflow is (as above) to create a second entry for that parcel. The mineral rights and surface rights columns do not actually indicate ownership; they only indicate whether the value found under PARCEL_TYPE applies to them.
2) The column refers to non-mutually exclusive characteristics: ownership (current, past, never--as it applies to the surface vs minerals) and access (whether we have an easement, lease, or permit). That is, we could have a parcel that we formerly owned that we now have a permit to access; with the current data structure, we could only indicate that by creating a copy of the parcel.
To address both of these issues at once, my recommendation would be to restructure the table like so:
PARCEL_ID RIGHTS_SURFACE RIGHTS_MINERAL ACCESS 46 NOT OWNED NOT OWNED EASEMENT 47 DISPOSED OWNED OWNED
This would prevent duplicate entries from referring to the same parcel, and--as far as I can tell--it would keep the possible values in fields mutually exclusive.
Is this a better (or the best) way to structure the data? And, if so, to what database management standards is this change conforming?
EDIT: More research has brought me suspect that this results from not normalizing to the 1st, 3rd, and Boyce-Codd normal forms (and that there are some domain/key normal form issues with the table as well). Maybe throwing around that terminology will be convincing enough.