6 Replies Latest reply on Aug 29, 2013 3:22 PM by Mark.Thompson

    Source Optional vs Target Optional


      Using SDDM


      I've got a situation with the Logical Model that is confusing me.  Can anyone shed some light for me?



      I have two Entities (i.e. the things that look like tables) in my logical model.  One table is Orders.  The other is Order Detail.



      If a row exists in the Order Detail table, it must be tied (via a PK/FK) to a row in the Orders table.  In other words, the Order Detail can't just be a random row -- it has to "belong" to an order.  There can be many order detail rows for a given Order (i.e. you can order multiple things on the same order, and each thing is stored on its own row in the Order Detail table).



      However, a single row in the Orders table doesn't necessarily have to be associated with any rows in the Orders Detail table.  For example, perhaps we just started the order and got interrupted before actually adding anything that we wanted to order.  So we can have an order number (PK in the Orders table) that doesn't yet tie to any rows in the Order Detail table.


      What I've just described seems to me to be a 1..0M, meaning that a single Order may be associated with any number of Order Detail rows, or none at all.  If the Orders table is on the left and the Order Detail table is on the right, I THINK I should see this connector: -|-----0<-


      I have set the Relation Properties as follows:


      Source Cardinality

      Source: Orders

      Source to Target Cardinality: - --<-*  (1 Order for many Order Details)

      Source Optional: UNCHECKED


      Target Cardinality

      Target: Order Detail

      Target to Source Cardinality: --1 (1 Order per Order Detail)

      Target Optional: CHECKED


      Now here's where my brain is getting all wonky: The O indicating an optional constraint is located on the Orders end of the connection line.  -|O-----<-   and to me, that feels backwards.  It feels like that's telling me that "multiple Order Detail lines can be connected to either zero or 1 order", and that's not correct.  An order detail line MUST be connected to an Order.  (Sure wish I could include a screenshot or two).


      I feel that the O should be on the Order Detail end of the line, which to me says "one order is associated with any number of detail lines, including zero".


      So to me, the position of the O feels wrong.


      I can move it into what I think is the "correct" position only by reversing the CHECKED and UNCHECKED status of the Source Optional and Target Optional boxes.  When I do that, the O moves, but the relation properties screen now appears wrong to me.


      I know this has to be really basic Data Modeling 101 stuff, but I'm just not getting it.  And I HAVE had my morning Starbucks, so that's not the trouble.


      Any help in getting me thinking straight?

        • 1. Re: Source Optional vs Target Optional
          Kent Graziano

          Which notation are you using in the diagrammer? SDDM support 3 notations - Barker, IDEF, and Information Engineering. It defaults to Barker (old Oracle*CASE) . Right mouse over white space in the diagram and look at which option is checked.

          • 2. Re: Source Optional vs Target Optional

            Information Engineering notation.

            • 3. Re: Source Optional vs Target Optional
              Philip Stoyanov-Oracle

              Hi Mark,


              as you said Order can exist on its own ( without having details) - then it should be set as optional in relationship (i.e "source optional" - checked).

              "Order details" entity exists only in the context of Order entity so it should be mandatory i.e.("target optional" not checked).

              Optionality is more clear in Barker notation (dashed part of line is always at optional side)- optionality symbol is at the other end of relationship in Information Engineering notation.i.e.

              setting Order as optional you'll get O symbol at the side of "Order details" entity. So if you have some doubts then switch to Barker notation.



              • 4. Re: Source Optional vs Target Optional

                Thanks, Philip.  Sorry, but your answer is still hammering against this stubborn brick wall between my ears.  Tell ya what... let's temporarily forget about the checkboxes on that dialog box and just discuss this in terms of human language, without a tool getting in the way.


                In your first sentence, referring to the Orders table, you stated that "Order can exist on its own", and then later when referring to the "Order Details" you stated that it exists only in the context of an Order.  I agree with both of those statements.


                But here's where it just isn't getting through my thick skull:  "Order can exist ... without having details ... [and therefore is] optional in the relationship."  And that's where, linguistically, I'm thinking something 180 degrees different.


                Let's assume that we have performed a table scan for the Order Detail table, and we find that it has one row. 


                Question: Based on the results of that Order Detail table scan, what do we immediately know about the Order table? 

                Answer: The Order table must have at least one row.  Right?


                Our row in the Order Detail table must be associated with a row in the Order table.  We know that to be true because we have defined a PK (Order) / FK (Order Detail) relationship.  The Order Detail row that we found couldn't even have been created unless we first had an associated row in the Order table so that the PKs/FKs can match up.


                In other words, it is mandatory that I have an Order row before I can even think about creating an Order Detail row.  So to my mind, if the Order row must exist first, then it is REQUIRED.  It is required that I have an Order row before I can have an Order Detail row.  And that's why I'm having trouble checking the box that says that the Order side of the relationship is optional. 


                And even if I have a required Order row, I don't have to have any Order Detail rows associated with it, which means that for a given Order row, Order Detail rows are optional -- there don't have to be any Order Detail rows.  Yet I must check a box that says they are mandatory.


                Do you understand my confusion?

                • 5. Re: Source Optional vs Target Optional
                  Philip Stoyanov-Oracle

                  In other words, it is mandatory that I have an Order row before I can even think about creating an Order Detail row.  So to my mind, if the Order row must exist first, then it is REQUIRED.

                  That's correct, you need to change the angle you are using to look at the picture.

                  Relationship defines association between two entities and optionality defines how they relate to that association. When we say Order is optional in that association that means Order can exist without that association.(without other end of relationship - who cares about details)

                  For details - if set it to mandatory , that means each instance of details can exist if there is corresponding instance of Order. Transforming it to relational model we'll get parent-child pair of tables (could be one table) and FK columns in child table will be mandatory if related relationship end is mandatory and optional if Details entity is set as optional in that relationship.



                  • 6. Re: Source Optional vs Target Optional

                    AH-HAH!!!  Now I get it.  If we forget Orders and Order Details and instead look at a list of Women and a list of Children, it makes more sense.


                    There is a one-to-zero-or-many relationship between Women and Children.   I have a list of Women.  For each woman, it is her option to have children or not.  The option rests with the Woman. 


                    But a child has no such option. If the child exists, it has no option as to whether or not it had a mother.


                    So the words 'Target Optional' do, in fact, mean 'The Target Is Optional'. If I am looking at one woman, it is indeed optional as to whether or not that woman has children.  Children (target) are not required (i.e. they are optional) for every woman (source).  Therefore, there will be an O on the relationship cardinality line, indicating that the relationship is optional.

                    What was hard to explain was the positioning of the O on the cardinality line.  The presence of the O simply means that the relationship is optional.  That much is easy.

                    But I was expecting the O to be positioned on whichever end of the relationship is the optional one (i.e. children are optional, so the O should be positioned on the children's end of the line), and that is not true.  The position of the O indicates which entity the option rests with.  (Which, I contend, is still backwards, but at least now I can explain it.  I don't like it, but I can explain it.)  The woman may, at her option, have one or more children.  That's the way to translate the cardinality line into spoken words when the O is on the woman's (i.e. source) end of the line. 

                    Philip, thank you for hanging in there with me.  Correct Answer awarded.