Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts

    Modeling: One or More vs. Zero or More

    Probably a simple question, but I'm a bit rusty on this.

    I am drawing an ERD using Crows-Foot notation, and am getting confused about the following...


    ----
    Let's say I have two entities: SECTIONS and SUBSECTIONS.

    And let's say I want it so that a SECTION or a SUBSECTION can exist without a corresponding entity.

    For example, I want my ERD to reflect that a SECTION can exist without any subordinate SUBSECTIONS.

    Likewise, a SUBSECTION can exist without having a parent SECTION.

    Normally a SUBSECTION would have a parent SECTION, and a SECTION would have child SUBSECTIONS, however "in the real world" it is possible that a SUBSECTION gets "decommissioned", so it would still need to exist in the database, but might not necessarily be actively linked to a SECTION.

    For example, maybe I have this relationship...

    SHOES (section) ---> GO-GO-BOOTS (subsection)


    While that may have made sense 30-40 years ago, needing a "Go-Go-Boots" subsection wouldn't apply today.

    So it seems to me that I would want my ERD to look like this to show that a SUBSECTION can exist on its own...

    Code:
    SECTION -||------0<- SUBSECTION

    Another case whee this would apply is if you added a new SUBSECTION but had not yet assigned it to a SECTION.

    If I recall correctly, if my ERD was draw like this...

    Code:
    SECTION -||------|<- SUBSECTION
    ...then that would mean that I could never have an entry in my SUBSECTION table that did not map to a corresponding SECTION.

    If that is correct, that would not mimic the real world, and would be unduly rigid.

    Hope that makes sense?

    Am I remembering what the notation really says, or am I mixing things up?

    Thanks,


    Debbie

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Well, as a pragmatist, I wouldn't care what the official word is. If I needed to represent a zero-to-many, I'd do it just as you described:
    SECTION -||------0<- SUBSECTION

    Makes eminent sense. Call it the double-d notation.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Hi Old Pedant. Long time, no speak!!

    Scratch everything I just typed in this *edited* response!!


    See, I was getting confused...

    *********************
    If I had this...
    Code:
    SECTION -||----|<- SUBSECTION
    It would mean...

    - A SECTION can have one or more SUBSECTIONS (but must have at least one).

    - A SUBSECTION must have one and only one SECTION. (So not "free-agent" SUBSECTIONS!!)

    Right?


    *********************
    If I had this...
    Code:
    SECTION -||----0<- SUBSECTION
    It would mean...

    - A SECTION can have zero or more SUBSECTIONS.

    - A SUBSECTION must still have one and only one SECTION. (So not "free-agent" SUBSECTIONS!!)

    Right?


    *********************
    In my OP, what I was really asking about was this... (I believe)

    Code:
    SECTION -0|----|<- SUBSECTION
    That should mean...

    - A SECTION can have one or more SUBSECTIONS (but must have at least one).

    - A SUBSECTION can have zero or one SECTION.

    So a "free-agent" SUBSECTION with no parent would be okay. And this would allow for something like this...

    Code:
    SECTION		SUBSECTION
    --------	-----------
    Shoes		Tennis Shoes
    Shoes		Sandals
    Shoes		Boots
    		Go-Go Boots
    Right?


    Again, I am probably being too "anal" on this, but it never hurts to ask such things?!


    I guess in practical terms, when you draw this....
    Code:
    SECTION -||----|<- SUBSECTION

    It is probably *implied* that you might have orphans if you so desired.

    Then again, maybe that is not implied, and it would be better to spell things out.

    The difference between having an orphaned "Subsection" is much different that an orphaned "Order"!!!

    What do you think?

    Sincerely,


    Debbie
    Last edited by doubledee; 09-17-2013 at 06:30 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    My head hurts.

    No, I do *NOT* think it is a good idea to have a child with no parent.

    I actually don't know any way to properly model that.

    What I would do, instead, is have the child attached to a parent that has no content. That is, a special parent that you can read to mean "there is no REAL parent for this child".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    My head hurts.
    Did I do that?


    Quote Originally Posted by Old Pedant View Post
    No, I do *NOT* think it is a good idea to have a child with no parent.
    Is that breaking some fundamental database rule?


    Quote Originally Posted by Old Pedant View Post
    I actually don't know any way to properly model that.
    I'm pretty sure that my notation above is correct. (It is what I remember back from college.)


    Quote Originally Posted by Old Pedant View Post
    What I would do, instead, is have the child attached to a parent that has no content. That is, a special parent that you can read to mean "there is no REAL parent for this child".
    That would work.

    Mind you, I'm not trying to go out of my way and have orphans, but in the context of what I've been trying to discuss, it doesn't seem like a big deal.

    If I was doing a Billing System with Customers, Orders, Products, etc then orphans would spell some larger organizational issue.

    But in my case, where this is for a website with online articles, I was thinking this might occur.

    For example, I might write an article entitled "Tax Preparers vs. CPAs" and need to store it in my database, but I may not be ready to assign it a Section, Subsection and so on for a number of reasons (e.g. It's not ready to be published yet, I need to speak to my Editor to see where she thinks the best places to place the article are, I'm waiting to see if there will be a dedicated "Accounting" subsection, and so on.)

    Follow me?

    For a DBA standpoint, maybe you would say, "Go write your article in MS Word, and when you figure out where you want it, give it to me and then I'll place it in the database!"

    But to me, I can see wanting to place articles in the database well before they are assigned a Section, Subsection, and so on.

    Make sense?

    So what do you say in response to that?

    Sincerely,


    Debbie

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Even if you can model the relationship, how do you code it?

    As a practical matter, I'd expect you to use
    Code:
    CREATE TABLE sub_section(
       ...
       sectionid VARCHAR(100) NULL, /* data type not significant for this discussion */
       CONSTRAINT FOREIGN KEY (sectionid) REFERENCES sections(sectionid),
       ...
    ) ENGINE INNODB;
    And MySQL (and SQL Server, by the by) *will* allow this even if sectionid in the sub_section table is NULL.

    So what the heck...if you can model it and you can also code it, why not?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Changing the topic slightly... (And maybe how I should have started things off?!)

    If I have the following relationship...
    Code:
    SECTION -||----|<- SECTION_DIMENSION ->|-----||- DIMENSION

    ...and I have some records in either parent table that do not have a corresponding counterpart (e.g. Section with no Dimension, or Dimension with no Section), is that a horrible sin??


    And would such records be considered an "orphan" the way an Order would be if there was no corresponding Customer?


    I'm asking these questions NOT because I plan on having a half-populated database, but rather because I want to make sure my ERD and my Data Model can appropriately handle real-world situations...

    While a Section should have several Dimensions and visa-versa, maybe I have a new Section for which I have not yet assigned Dimensions?

    Because the aforementioned tables - and others - relate to how my website is laid out and how to find Articles, I guess I feel that it is less of an issue to have a solo Section, Dimension, Subsection, etc (I mean you could use them if they don't link to something, but maybe I stick them in the tables for later use, or while I flesh out how my website should look and feel.)


    By contrast, you would not want to do that when relating Customers to E-mails to Orders to Products, etc.

    Not sure if I am making any sense. (It's a drowsy afternoon...)

    Just trying to make sure my Data Model is *flexible* as I prepare to go live with a new release - one which will likely change after people start using it!

    Sincerely,


    Debbie
    Last edited by doubledee; 09-17-2013 at 09:20 PM.

  • #8
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Maybe all of this is dumb and there should always be matching values between Parent Tables and a Junction Table?!

    And until I build a Form to enter values into my database, maybe I just periodically need to run Outer-Joins against Parent Tables and Junction Tables to make sure I don't have any orphans.

    If I do, I should probably fix that issue.

    Just trying to test out various scenarios and make sure I have a Database and Code that will handle things, OR making sure I do things in a way that won't break how I have things set up.

    Not sure if that makes any sense?!

    Sincerely,


    Debbie

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I don't see anything wrong with, to match your example, having a section with no matching records in section_dimension. And your example of how you would likely REALLY use this (e.g., creating a subsection but not yet being sure what section to put it into) makes tons of sense.

    I would think that, so long as you indeed have a mechanism to detect orphans (so you can decide what to do with them, if anything), you are doing just fine.

    Remember, I'm much more a pragmatist than a theorist. I can follow the theory, but I'm much more interested in getting a working solution.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I don't see anything wrong with, to match your example, having a section with no matching records in section_dimension. And your example of how you would likely REALLY use this (e.g., creating a subsection but not yet being sure what section to put it into) makes tons of sense.

    I would think that, so long as you indeed have a mechanism to detect orphans (so you can decide what to do with them, if anything), you are doing just fine.

    Remember, I'm much more a pragmatist than a theorist. I can follow the theory, but I'm much more interested in getting a working solution.
    Okay, I think I'm good with things.

    As always, a BIG THANKS!!

    Sincerely,


    Debbie


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •