Okay, I have to get this right but I am a little unsure. I'm dealing
with a set of "Standard" codes which are both
* incomplete, so some users of the coding system add a few codes of
their own (including ones specific to our company),
and
* changing, so once complete set of codes will be newly revised next
quarter.
Not every source supplies a fulll set of codes, and at the moment there
is a hierarchy. IOW, if the same code value comes from more than one
source, then for example in most cases the data from the standards
committee is preferred, then a second source, then a few come from a
third and lastly come our own company codes.

A lot of the users of this standard are still living on spreadsheet
applications, so even the standard committee doesn't see the problem
of being too flexible. 8^)


So to deal with this situation, I am considering the following two data
models. I think both have some weaknesses, so other suggestions are
welcome

Consider that each code at a minimum has three attributes:
code#
code description
source (either a new revision of the standard or a custome code
specific to one company or group)

There is a set of codes for a parts hierarchy, and a set of codes
describing the location of the parts. So my application will need an
entity to identify where each part is located (it can be more than one
place. (NOTE: the Component/Location relation is only one of many in
this application.)

Model A
- here the driving concept is that a given code# might exist from
several sources, and all of them are made available to the application.
So each type of code must propagate with its source indicator. This
spreads extra fields to every table that uses these codes and puts a
burden on the applications to correctly select the right source or to
at least check an Active attribute which would give a centralized
control.

entity LOCATION
PK codeL
PK source
description
active

entity COMPONENT
PK codeC
PK source
description
active

entity COMP LOC
PK codeL FK LOCATION
PK sourceL FK LOCATION
PK codeC FK COMPONENT
PK sourceC FK COMPONENT

entity ORDER
PK ORDER NUM
codeC FK COMPONENT
sourceC FK COMPONENT
codeL FK LOCATION
sourceL FK LOCATION
-----------------------


Model B
- here the driving concept is that while a given code# might exist
from several sources, only one of them must be usable. So each type of
code essentially has a staging table and a master table (rules for
which source becomes the master can be ignored, only consider that just
one source is used).

entity LOCATION
PK codeL FK LOCATION MASTER
PK source
description

entity LOCATION MASTER
PK codeL
source
description

entity COMPONENT
PK codeC FK COMP MASTER
PK source
description

entity COMP MASTER
PK codeC
source
description

entity COMP LOC
PK codeL FK LOCATION MASTER
PK codeC FK COMP MASTER

entity ORDER
PK ORDER NUM
codeC FK COMPONENT
codeL FK LOCATION
----------------------------

So the application might have the user select a component and then
present a list of locations where the component was placed. Eventually
these codes are transmitted back to the sources and other consumers of
the data.

The advantage of Model B is less data to move around in the tables, at
the risk of selecting the wrong code for a particular consumer of the
data. The risk is pretty low.

The advantage of Model A is allowing the application to select the
right codes within the context which it runs at the risk of selecting
the wrong codes (by user or programming error) and moving more data
around the tables.

I fear my other alternative is to abadon DB enforced RI (which is how
most of the tables in this application currently exist). As much as I
promote proper datamodeling, I am not a guru at it.

At the moment I am leaning towards model A, but I'm likely to get
hassled in my group to justify why we need the source as part of the
primary key.

Help, please.
Ed