Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.

Welcome, Guest
Guest Settings

Thread: Do not resolve many-to-many relationships in physical model

Permlink Replies: 0
Brian Bertalot

Posts: 3
Registered: 3/22/13
Do not resolve many-to-many relationships in physical model
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 19, 2016 9:47 AM
Hello All,

I was wondering if anyone has come across a way to NOT resolve many-to-many relationships when generating a physical model from a logical model. It gives you 3 options for many-to-many relationships but in my case I want it to NOT resolve those relationships. I know it is never recommended to have many-to-many relationships at the database level. However, in my case it's sort of unique. I need to show the true data relationship between tables - in this case it's between a person table and a bridge table. The bridge table has a durable key from the person table that we are using instead of the primary key. This is because of how the data is and we need it this way. However, the person table is history tracking which will only have one row of data per person active at one time but will have the same durable key for all of a person's rows.

IE: If I have data in the person table, it'd look like this:

PK Durable Key Current Row Indicator Effective Date End Date
1 1 N 1-1-2001 1-2-2001
2 1 N 1-3-2001 1-25-2001
3 1 Y 1-26-2001 -

In the bridge table we have that person durable key, not the PK so it creates a many-to-many relationship between the 2 tables as most of the persons will have many rows in the bridge table. The tricky part is that on our reporting tool, we will pass a date in that will only show the person row that was active at a point in time. IE: If I pass a date of 1-10-2001 into the reporting tool, it'll show me the person row with the PK value of 2 even though it's not the current row. However, at the database level when writing a query against the 2 tables, if you don't pass a date in, it'll create that Cartesian product because it's a many-to-many relationship. For this relationship between these 2 tables, we need to show the many-to-many relationship for query writing purposes for the more technical people that won't be using the reporting tool. The problem is that ER Studio automatically resolves the relationship by forcing you to select 1 of 3 options before it'll generate the physical model. We do not want to create an associative entity for this purpose. Does anyone know a way around this? I know ER Studio is trying to be nice and trying to enforce the rules of modeling, but sometimes, depending on your data, you need to break the rules for unique solutions to happen.

Any help on this would be greatly appreciated!

Thank you,
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02