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
