Relations on Unique Constraints
Frans,
Is there a reason why you can't define relations on unique constraints? I have a requirement (as a result of my performance refactoring again 
) to implement a m:n relationship using a Unique Constraint field like:
+-------+ +-------+ +--------+
|PK | | PK |-----| PK |
|FK UC |------| PK | +--------+
+-------+ +-------+
The UC relations don't get picked up by the designer... is there any way to "cheat" the designer into allowing the relation?
Marcus
Marcus wrote:
Frans,
Is there a reason why you can't define relations on unique constraints? I have a requirement (as a result of my performance refactoring again
+-------+ +-------+ +--------+ |PK | | PK |-----| PK | |FK UC |------| PK | +--------+ +-------+ +-------+
Cool ascii art! 
The UC relations don't get picked up by the designer... is there any way to "cheat" the designer into allowing the relation?
Marcus
It's not the UC, it's the PK side that's not correct. In your setup, the PK side is not unique, as the PK side is not the complete PK. This means that a single value in the first table (with the FK), points to multiple PK rows. The PK side should always have as much fields as the FK side in a relation.
Otis wrote:
Cool ascii art!
Like the days before HTML Email!!
Otis wrote:
It's not the UC, it's the PK side that's not correct. In your setup, the PK side is not unique, as the PK side is not the complete PK. This means that a single value in the first table (with the FK), points to multiple PK rows. The PK side should always have as much fields as the FK side in a relation.
Yes you are correct the PK side (intermediate table) is not unique, but I don't want it to be. I would expect to have a collection in this case...
Essentially in my set up there is another table (Table D) which sits between Table A (Left) and Table B (middle). This table D has a 1:1 relationship with Table A, but for permormance reasons I would like to bypass table D and allow the unique FK in Table A to have a direct relation to Table B yielding a collection. I can define these realtionships in SQL Server, but as I mentioned LLBLGen Designer does support them... 
Fast forward to 2005... Sorry, no more ascii art
I've marked the relation SdsArk_Item.ItemFileUID (FK-UC) and SdsArk_ItemFileItemServerLink.ItemFileUID that LLBLGen does not pick up... Is there any way to trick the Designer into seeing it?
UPDATE: Even if the designer didn't pick it up automatically, but allowed me to create it in the Designer?
Marcus wrote:
Otis wrote:
Cool ascii art!
Heh indeed. HTML email, I always convert it to ascii... why people still want to reply emails in html is beyond me.
Otis wrote:
It's not the UC, it's the PK side that's not correct. In your setup, the PK side is not unique, as the PK side is not the complete PK. This means that a single value in the first table (with the FK), points to multiple PK rows. The PK side should always have as much fields as the FK side in a relation.
Yes you are correct the PK side (intermediate table) is not unique, but I don't want it to be. I would expect to have a collection in this case...
ah, the UC is the PK side and the PK field in the intermediate table is the FK side? Otherwise I'm confused.
Nevertheless, it's not something that's supported, in the way you modelled it.
Essentially in my set up there is another table (Table D) which sits between Table A (Left) and Table B (middle). This table D has a 1:1 relationship with Table A, but for permormance reasons I would like to bypass table D and allow the unique FK in Table A to have a direct relation to Table B yielding a collection. I can define these realtionships in SQL Server, but as I mentioned LLBLGen Designer does support them...
Fast forward to 2005... Sorry, no more ascii art
I've marked the relation SdsArk_Item.ItemFileUID (FK-UC) and SdsArk_ItemFileItemServerLink.ItemFileUID that LLBLGen does not pick up... Is there any way to trick the Designer into seeing it?
UPDATE: Even if the designer didn't pick it up automatically, but allowed me to create it in the Designer?
No, that's not going to work. SqlServer lets you define multiple FK's on a field, pointing to different PK's, as you're doing in your model. Which IMHO is not correct, as an FK side has just ONE PK side for referential integrity.
IF you want to eliminate the extra join, I'd simply move ItemFile into Item. The thing is, that would be correct, as the relation ItemFile - Item is 1:1. Then, you can simply keep Item-Server relations using the intermediate table you've now.
Though as with everything performance: as soon as you're start making concessions to get more performance, there is no end to it: there's always one more concession you can make to get more performance.
So, IF you're willing to break down the relational model for performance, I'd merge Item and ItemFile.
Otis wrote:
Nevertheless, it's not something that's supported, in the way you modelled it.
OK...
Otis wrote:
IF you want to eliminate the extra join, I'd simply move ItemFile into Item. The thing is, that would be correct, as the relation ItemFile - Item is 1:1. Then, you can simply keep Item-Server relations using the intermediate table you've now.
Ahh but I didn't show you ALL the tables or ALL the fields on SdsArk_ItemFile 
...
I guess I'll have to subclass SdsArk_ItemEntity and add a new EntityCollection Property to hold the SdsArk_ItemFileItemServerLink entities. I don't mind having to write the fetch logic manually, its just a pain to have to subclass then entity in order to hold the extra collection.
True, though that's one of the concessions you're doing to get a bit of extra performance out of the database.
Before you dig into the code to get it implemented though, I'd test if this optimization is worth it.
For example, if you're using 98% reads and 2% writes, you can create an indexed view and use that view for fetching the data. This will then greatly increase speed for fetches. Be sure to set the arithabort option to true.
Otis wrote:
IF you want to eliminate the extra join, I'd simply move ItemFile into Item. The thing is, that would be correct, as the relation ItemFile - Item is 1:1. Then, you can simply keep Item-Server relations using the intermediate table you've now.
I've decided to go with this suggestion... Thanks.
LLBLGen together with ReSharper makes this schema change simple to implement.
BTW - for anyone wanting to make a drastic change like this. I recommend using the following approach:
1) Make changes in Database 2) Refresh Calalog in Designer but DO NOT GENERATE CODE yet 3) Using ReSharper's Rename Method, rename the entities to the new names matching those in the Designer 4) Ensure you have a compile 5) Generate Code 6) Compile again to make sure everything is still ok
You cannot use Rename Method AFTER the code is generated as the method to rename no longer exists...
Good tip! 