Note to long-time forums regulars: This is basically a rephrasing of my old question in my neverending quest for normalizing conflicting models & business requirements. I'm posting mainly to get some simulation on my thoughts among the way and see how I can improve and seeing what others may have done in solving similar problem to this.
After examining things a bit more closely, I realized I actually had a many side table which I needed to be shared between two one side tables that has nothing to do with each other. The many side table is primarily a log table, while the two one-side table has nothing in common and are not related at all. Their relationship to the many-side table is primarily a mean for reference.
My solution was to set up the many side as thus:
ID (Primary Key)
OneSideTableA (Foreign Key to TableA)
OneSideTableB (Foreign Key to TableB)
.... (some fields for log stuff)
The relationship are defined for the respective table, guaranteeing that there won't be orphans, and I have a trigger in place to do a XOR test to ensure that I don't end up with both missing or both with some ID from either table. (This isn't really necessary as I use two separate forms for the respective updates/inserts, but I feel better having the extra constraint... just in case I do something stupid a year later, and get a hearty headsmack in return)
Seems pretty straightforward and easy to setup, though in further analysis, this actually violates the normalization on two different fronts.
First, I have a semi-repeating field (OneSideTableA & OneSideTableB) and this could conceivably be restructured thus:
ID
ParentTable (Identifies the parent table)
ParentID (stores foreign key)
....
But I no longer have a mean to establish a relationship.
Second, I suspect purists would argue that if I had two one-side tables sharing same many side table, the two one-side tables should be combined into a single table.
Indeed, that was my old solution. I defined a base table which would hold an entity and stored detailed information about an entity in a one-one table then related all many-side tables to that base table. (If you have no idea what the hell Banana is rambling on about, search the forum for three legged tables) After considerable analysis (thanks again, The_Doc_Man), that idea was showed to be defunct. Furthermore, there is no good reason to have a one-many side between the 'base' and 'detail'.
So, hence my current solution which sure looks fishy to me!
Will welcome any Deep Thoughts on this puzzle.
After examining things a bit more closely, I realized I actually had a many side table which I needed to be shared between two one side tables that has nothing to do with each other. The many side table is primarily a log table, while the two one-side table has nothing in common and are not related at all. Their relationship to the many-side table is primarily a mean for reference.
My solution was to set up the many side as thus:
ID (Primary Key)
OneSideTableA (Foreign Key to TableA)
OneSideTableB (Foreign Key to TableB)
.... (some fields for log stuff)
The relationship are defined for the respective table, guaranteeing that there won't be orphans, and I have a trigger in place to do a XOR test to ensure that I don't end up with both missing or both with some ID from either table. (This isn't really necessary as I use two separate forms for the respective updates/inserts, but I feel better having the extra constraint... just in case I do something stupid a year later, and get a hearty headsmack in return)
Seems pretty straightforward and easy to setup, though in further analysis, this actually violates the normalization on two different fronts.
First, I have a semi-repeating field (OneSideTableA & OneSideTableB) and this could conceivably be restructured thus:
ID
ParentTable (Identifies the parent table)
ParentID (stores foreign key)
....
But I no longer have a mean to establish a relationship.
Second, I suspect purists would argue that if I had two one-side tables sharing same many side table, the two one-side tables should be combined into a single table.
Indeed, that was my old solution. I defined a base table which would hold an entity and stored detailed information about an entity in a one-one table then related all many-side tables to that base table. (If you have no idea what the hell Banana is rambling on about, search the forum for three legged tables) After considerable analysis (thanks again, The_Doc_Man), that idea was showed to be defunct. Furthermore, there is no good reason to have a one-many side between the 'base' and 'detail'.
So, hence my current solution which sure looks fishy to me!
Will welcome any Deep Thoughts on this puzzle.