Re: Design database schemas around a common subset of objects

On Mon, 8 Nov 2021, Erik Wienhold wrote:

> One thing I already know that may differ is how modelled objects are named
> in the real world. We deal with plots of land, hence the geographic data.
> Depending on the region, these plots may use different naming schemes
> relevant to legal documents. The core objects would use a generated
> surrogate key but each project would have a dedicated relation that maps the
> real-world names of those plots to their surrogate keys. The real-world
> names can be normalized into multiple attributes instead of just storing
> names as text. This normalization may vary between projects, e.g. different
> number of attributes.

Erik,

How about a table 'plot' that holds the real-world name with an abbreviation
(as the PK) and other information relevant to plots? That seems to be what
you're describing above but explained differently. Land plots, like othter
geographic locations, always have surrogate keys because there's no natural
key associated with it. I use site_nbr, samp_nbr, and similar PKs because
there's nothing inherently associated with those attributes. Here in the US
using SSN (social security numbers) for people are a natural key as is the
VIN (vehicle identification number) for ... vehicles.

Rich