As a reasonably new user of Aurelius, I am still struggling with the concept of IDENTIFYING and NON-IDENTIFYING relationships. Unfortunately, the documentation is not very exhaustive and (at least in my experience) somewhat ambiguous on this subject.
I have therefore listed some cases below with some assumptions. I would highly appreciate if you could comment whether these assumptions are correct or, if not, provide some guidelines to correctly set up such a case.
Let?s assume we have class TModel (table Model). For related classes tables I can think of the following cases, I would typically need:
Case A - One-to-many
Class TItem, table Item These are details where TModel is the master.
Both tables have a (unique) primary key on field ID. In my case it's almost always a TGUID (for data replication/exchange), but I think that's irrelevant to this question.
I create a foreign key on TItem to TModel; a NON-EXCLUSIVE index FK_Model_Item on field TItem.ID_Model.
In Data Modeler, I create an IDENTIFYING relationship with parent TModel and child TItem, using TModel.ID and TItem.ID_Model.
Case B - One-to-one (unique)
Class TSettings, table Settings This would be an entity (table) where each TModel only has 0 or 1 TSettings. Each TSettings may only be used (referenced) by one TModel.
Again, both tables have a (unique) primary key on field ID.
I add a field ID_Settings to TModel.
I create an index IDX_Settings on TModel.ID_Settings. I assume this should be EXCLUSIVE (or UNIQUE) to ensure each TSettings is only used by TModel.
In Data Modeler, I create an IDENTIFYING relationship with parent TModel and child TSettings, using TModel.ID_Settings and TSettings.ID.
Because of the unique one-to-one relationship, I would set options to Cascade; at least for Delete.
In case I want to enforce TModel to always have one TSettings (i.e. not zero), I could upon creation of a TModel object, create a TSettings and assign it to the TModel.Settings property (in application code).
Case C - One-to-one (non-unique)
TLookup This would be an entity (table) that serves to provide a one or more lookup values from a database table whereby multiple TModels may reference the same TLookup entity. The reference may be NULL (i.e. it's not required to set a TLookup reference in TModel).
Again, both tables have a (unique) primary key on field ID.
I add a field ID_Lookup to TModel.
I create an index IDX_Lookup on TModel.ID_Lookup. In this case this should be a NON-EXCLUSIVE index so that multiple TModel's may reference the same TLookup.
In Data Modeler, I create an NON-IDENTIFYING relationship with parent TModel and child TLookup, using TModel.ID_Lookup and TLookup.ID.
In this case, I don't see a specific need for an index on ID_Lookup, but I presume that's just a requisite for the Data Modeler to work.
May be I am misunderstanding some concepts, but since the data model (and entity classes) will be the foundation of some (large) applications I plan to build with it, I think it's important to thoroughly understand these concept and build the models correctly from the start.
In addition, this post may help other (new) users in the future. Thanks in advance for your comments!
To clarify: an IDENTIFYING relationship is one where the foreign key is part of the primary key. For example, you have a table MODEL, field ID which is the PK. Then you have a table ITEM where the PK is "ID" and MODEL_ID (the FK to MODEL). This is an IDENTIFYING relationship, the FK field (MODEL_ID) is part of the PK of Items table. Since you are using guids and not using composite keys (which I strongly recommend no matter what, even more when using Aurelius), you will never use IDENTIFYING relationships: all your relationships will be NON-IDENTIFYING.
Thus, almost all of your assumptions is correct. Here are the "fixed" ones:
Case A:
1. Ok
2. Ok
3. No, create a NON-IDENTIFYING relationship
4. You can also add CascadeTypeAllRemoveOrphan cascade (which include TCascadeType.Remove).
Case B:
1. Ok
2. Ok
3. Ok
4. Create a NON-IDENTIFYING relationship
5. Ok
6. Yes, or you could simply set the ID_SETTINGS field as NOT NULL
First of all, thanks for the very quick response on a weekend day! Your comments are certainly clarifying a lot, but I have some remaining comments / questions (I think they might be more related to Data Modeler, but I will leave them in this post to keep everything in context):
Case B
I was intially still struggling with this, but after inspecting some demo models (Data Modeler), it appears that the TSettings entity is best used as the Parent. This maps TModel.ID_Settings to ID_Settings of type TSettings. I manually rename this property (ID_Settings -> Settings) in the TMS Aurelius Exporter for clarity (for myself). The Exporter creates as SchematicList property which is unchecked (i.e. do not export) by default. I leave this as-is, since it makes no sense to have this list.
I do not need it in this context, but how would I created a reference to TModel in TSettings? That does not appear to be possible as it involves the PK, which is already used for the ID property, but I can imagine that in another case it may be that you can obtain the TModel equivalent from the TSettings equivalent.
Case C
You say there is no need to create an index on field ID_Lookup. However, the Data Modeler only allows defining relationships on fields that are part of an index (unless I am missing something). As such, the only option I see is to NOT define a relationship (in Data Modeler) and map field ID_Lookup to a property Lookup of type TLookup. This leaves a "loose coupling" with the Lookup entity/table which kind of makes sense for a lookup scenario; the Lookup property of TModel can be set to a Lookup entity so that on persistence to the DB, field ID_Lookup is set to the ID (GUID) of the Lookup entity.
1. Isn't it the same as you described previously? TModel.ID_Settings as a FK to Settings.ID.
2. You should allow for the list to be created (Settings.ModelList). This will provide you with the list of all models associated with the settings (which will be always a list of one element).
Case C
1. The fields must be part of an unique index in the parent. Either the PK, or an unique index. You don't need to have an index in the child (the foreign key).
I think I now fully understand the way it all should be set up.
B1; It was almost the same. What I was trying to say is that the crux is to select TSettings as the Parent and TModel as the Child when defining the relationship. This then generates the properties (i.e. specifically a Settings: TSettings property in the TModel class) as I would expect.
B2; Clear, I just had to realize a list can also contain just 1 value ;-)
C1; As B1.
I suppose, referring to B1 & C1, that creating an index on the child field (e.g. TModel.ID_Lookup) depends on specific needs. For example, if one often requires to retrieve all TModels related to a TLookup, it makes sense to have an index on TModel.IDLookup. If there is never a need to do so (e.g. TSettings always retrieved through TModel), it makes sense not to generate an index and avoid the overhead of maintaining it.
Overall, as you can see, I was too focused on TModel being the Parent. Probably due to many years of "traditional" database design. Doing it the correct way (and fully understanding Identifying and Non-identifying), I am ready to build some larger and robust models for my applications.