November 2nd, 2011, 04:52 AM
Shared foreign keys across associated models
I am looking for a pattern or approach to a design problem I have where I have been asked to assign a "location" foreign key (location_id). to both a "group" and an "item" model
A group contains many items and since both item and group have a location attribute one would think I simply remove the location attribute from the item and inherit it from its group...
I have been assured that there is no functional meaning with the group having a location (other than for display purposes location=>group=>items) but the location(_id) of the item is used extensively in its operation and must be sustained
As an aside.. the group table is self-referencing with a (parent_id) to create a hierarchy so location_id cease to be relevant on any child (group) record.
Would welcome any thoughts or comments.
November 7th, 2011, 09:28 PM
Just an FYI...
Working on a polymorphic approach where a location and group effectively become a location_group and use this table as the hierarchical structure. This means I build the top level (root) location_groups using the locations and use the groups as children of those.
This allows me to 86 the the location_id attribute but maintain the tree display.