Multiple tables related to same field.
Hello, I hope this is clear.
I have a database that is used in 12 different offices, each with their own instance of the database for regulatory purposes. I have 'lists' which are composed of 'list elements' used throughout the application, nearly every table relates to the list elements one or more times.
There are two types of 'list elements', user defined and system defined. System defined elements cannot be changed and are used for defaults, constants in stored procedures or constants in the application. A list can be composted of both types of elements and are purely used to populate combobox/listbox controls in the application.
I currently use four tables for this structure
1) ListDefinitions (ID (PK), Name, DefaultElement)
2) ListElementsUserDefined (ID (PK), Name) ID starts at 1000
3) ListElementsSystemDefined (ID (PK), Name) ID starts at 2000000
4) ListElementsAssignments (ID (PK), ListID (FK to table 1), ElementID)
I currently have a view which unions tables 2 and 3 that I use for joins in queries.
I need the system elements to have a consistent ID between the database instances, or else upgrades would be a nightmare. User defined elements can be different in each office, in both quantity and content. All elements can never be deleted.
Is there a better way to handle this situation that will allow my set relationships?
