2

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.

1

The best argument against the existing design is that there's really no good primary key on the table (except perhaps an identity column, which I don't consider a good primary key). That makes it hard to join to other tables.

An advantage with your design is that a query can easily identify parcels where you (for instance) have mineral rights but not surface rights using a simple WHERE clause. With the existing design, this is still possible, but it's a lot messier (and not really feasible using graphical tools or entry-level SQL). This is particularly important if the table in question is a dimension table in a datawarehouse.

1
  • Great point about the primary key! That's part of what I was trying to verbalize. I think the current data managers don't see it that way, because it's a GIS dataset, and they're thinking of the location as a primary key. But, to me, that's all the more reason to avoid duplicate features: the overlapping features are causing issues with spatial analysis. – Tom Feb 11 '16 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.