I need to put a predetermined number of even more attributes to help you for each and every pair when you look at the a preexisting many-to-many register. Missing into diagrams below, and this from Solutions step one-4 is the greatest method, when it comes to benefits and drawbacks, to do so of the stretching the bottom Case? Otherwise, can there be a far greater option I have not noticed right here?
We today need certainly to include a lot more backlinks to help you features that belong for the pair of existing objects. I’ve a fixed number of such characteristics per few, regardless of if one to entryway from the property dining table can get affect several pairs (or be put many times for starters few). I’m seeking determine the best way to do that, and you may in the morning having problems sorting aside tips think about the condition. Semantically it appears to be since if I could describe it one of one’s adopting the just as well:
- You to partners about one to band of a predetermined number of additional properties
- One pair linked to of a lot extra properties
- Of a lot (two) things linked to that group of properties
- Many stuff associated with of a lot qualities
We have one or two target versions, X and you may Y, per with unique IDs, and you will a linking table objx_objy with columns x_id and you may y_id , and this with her function an important trick on the link. Per X will likely be about of numerous Ys, and you will the other way around. This is actually the setup for my established of a lot-to-of a lot relationships.
Ft Circumstances
Today on the other hand I have a couple of properties defined an additional dining table, and you can a set of conditions under hence certain (X,Y) couple have to have assets P. The number of requirements is fixed, additionally the same for everyone pairs. It essentially say “When you look at the state C1, couples (X1,Y1) enjoys possessions P1”, “Within the state C2, few (X1,Y1) provides possessions P2”, and stuff like that, for a few things/standards for each pair in datingranking.net/couples-hookup-apps the sign-up table.
Option step one
In my newest problem you can find precisely three such requirements, and that i have no cause to expect one to to improve, very you to possibility would be to add articles c1_p_id , c2_p_id , and you can c3_p_id so you’re able to featx_featy , specifying to possess certain x_id and you can y_id , and that possessions p_id to use from inside the each of the about three cases.
It doesn’t feel like a good idea in my experience, as it complicates the fresh SQL to choose all the functions placed on a component, and you can doesn’t easily level to help you far more standards. However, it can enforce the requirement from a certain number of criteria for every single (X,Y) pair. Indeed, simple fact is that only choice right here one really does very.
Solution 2
You to disadvantage to it is which will not identify the quantity from standards for every pair. Other is the fact as i in the morning just considering the first relationship, that have something similar to
Then i have to create a distinct condition to cease backup records. This seemingly have destroyed the point that for each pair is always to exist only once.
Alternative 3
Create a different ‘pair ID’ regarding signup desk, and have now an extra connect desk involving the first you to definitely as well as the properties and you can conditions.
Which appears to have new fewest drawbacks, except that the possible lack of enforcing a fixed number of standards each couple. Can it seem sensible even when which will make an alternate ID one to means absolutely nothing apart from existing IDs?
Choice cuatro (3b)
Simply the just like Option step three, but with no creation of the excess ID job. They do this by getting each other completely new IDs on the the brand new signup dining table, it include x_id and y_id sphere, instead of xy_id .
An additional advantage to that particular function is that it generally does not change the existing tables (though they aren’t during the production yet). But not, they basically copies a whole table multiple times (otherwise seems this way, anyway) very together with doesn’t seem most useful.
My personal impact would be the fact Alternatives step 3 and you can cuatro is similar sufficient that we might go which have just one. We probably would possess at this point otherwise for the requirements off a tiny, fixed number of links so you’re able to features, that renders Solution 1 hunt more sensible than they if you don’t carry out feel. According to particular not a lot of testing, including a distinct clause to my requests will not frequently feeling abilities in cases like this, but I’m not sure one Option 2 stands for the trouble just like the well while the other people, by inherent duplication considering establishing an identical (X,Y) pairs during the numerous rows of your own connect desk.