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.
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Need help with Many-to-Many-to-Many

    I am revamping my website and how Articles get categorized and stored.

    While my new approach makes sense to me, it may take some explaining here?!

    My website will ultimately have thousands of Articles on a plethora of topics related to Small-Business. (Think "NY Times" on steroids!!)

    So, in order to manage all of this content, my website is divided into these "Sections":

    Sections:
    Code:
    - Finance
    - Legal
    - Management
    - Operations
    - Marketing
    (This list could change, but it is unlikely.)


    Because of all the topics I will be covering on Small-Businesses, I came up with maybe 100 "Sub-Sections" - which is pretty unwieldy?!

    So, to group things together, I created an *intermediary* concept called a "Dimension", which is a grouping of "Sub-Sections"

    (**NOTE: A key concept to get here, is that on the Back-End I am breaking down Articles down into 3 groupings: Section > Dimension > SubSection. However, in my URL, I am only displaying 2 groupings: Section > SubSection This maybe makes my database a little tricker...)


    In database-speak, here is what I have so far...


    Entities:
    Code:
    - SECTION
    - DIMENSION
    - SUBSECTION
    - ARTICLE

    Business Rules:
    Code:
    - One SECTION can have one or more DIMENSIONS
    
    - One DIMENSION belongs to one or more SECTIONS
    
    - Some DIMENSIONS will only every map to one SECTION
    (e.g. Accounting subsection ---> Finance section)
    (e.g. Featured_Legal subsection ---> Legal section)
    
    - One DIMENSION can have one or more SUBSECTIONS
    
    - One SUBSECTION will likely only map to one DIMENSION
    (But I am allowing for one SUBSECTION to have one or more DIMENSIONS)
    
    - One SUBSECTION will have one or more ARTICLES
    
    - One ARTICLE will have one or more SUBSECTIONS
    
    - An ARTICLE cannot exist outside of a SUBSECTION

    Relationships:
    Code:
    SECTION -||-------|<- SECTION_DIMENSION ->|---------||- DIMENSION
    
    DIMENSION -||------|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION
    
    ??? ->|------||- ARTICLE

    So the $10,000 Question is: "How do I join together these Four Entities to get the desired end result I need/want??"


    Observations:
    1.) Having SECTION_DIMENSION is good, because it defines which combinations of Sections and Dimensions can exist.

    (Remember, this can't be a "free-for-all"... You couldn't have the dimension "Featured_Legal" mapping to "Finance")


    2.) Having DIMENSION_SUBSECTION is good, because it defines which combinations of Dimensions and Sub-Sections can exist.

    (Again, this can't be a "free-for-all"... You couldn't have the sub-section "Payroll" mapping to the dimension "Business-Structure".)


    3.) An ARTICLE ultimately needs to be tied to a SECTION, a DIMENSION, and a SUBSECTION.

    (Or, at the very least, some *valid* SECTION and SUBSECTION combination as defined in the junction tables: SECTION_DIMENSION and DIMENSION_SUBSECTION


    4.) If I have the junction tables: SECTION_DIMENSION and DIMENSION_SUBSECTION then I need to be careful not to have similar Junction Tables which might *duplicate* these, and then be a real nightmare as far as keeping things in synch?!


    5.) Part of me is tempted to just create a Monster Junction Table like this...

    SECTION_DIMENSION_SUBSECTION_ARTICLE


    So this is where I am stumped...


    I feel like everything I have described is solid, but when it comes to tying all 4 Entities together, that is where I get confused...

    Please help me out, o Coding-Forums Database Gurus!!!

    Sincerely,


    Debbie

    P.S. If it helps any, I can provide sample data to help you visualize things better...

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,515
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    Yes, some sample data would help a lot.

    I'm not clear that you need all the tables you are describing, but if you think you do, you probably do. So sample data would help make that clearer.
    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,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Yes, some sample data would help a lot.

    I'm not clear that you need all the tables you are describing, but if you think you do, you probably do. So sample data would help make that clearer.
    Sure, I'll post some sample data in a moment.

    But first, allow me to *try* and explain my "Information-Architecture" thought-process... (Be gentle, because I spent A LOT of time on this, and feel it is right.)

    About a month ago I finished QA'ing my website and was ready to "go live". (All I had left to do was drop in my Data/Content.)

    But when I went to go do that, TRAGEDY struck?!

    You see, over the last 18-24 months I have been hand-coding my website, my creative side has never slept. And what was supposed to be a modest website with 6 Sections/Navigation tabs had turned into one with 40-50 main topics, plus all of the related ones?!

    I had no physical way to cram all of that into my website design. (Plus, I'd lose people if they had to drill down 5 levels to get to everything I came up with as far as content?!)

    So I spent 2-3 weeks racking my brain, coming up with lists and lists of things, and drawing everything out on paper.


    One KEY PROBLEM I faced was that there were all of these different ways to view things...

    For example, is it a Car? Or a Sedan? Or gas-powered? Or American-made? Or red? Or fuel-efficient?

    Basically what was supposed to be a *simple* website on Small-Business articles had exploded into something like the "NY Times"...


    But eventually, some light-bulbs went off in my head, and I came up with this approach...

    1.) Divide my content into 6-8 major "Sections". (These would easily fit into my horizontal navigation tabs.)

    2.) Instead of creating 3, 4, 5 levels in my Information Architecture, keep my URL's to 2 levels like this...

    Section > SubSection > Article

    3.) Instead of creating a really DEEP website, group like things together into a concept called a "Dimension" and spread things out HORIZONTALLY...

    4.) When a person clicks on a "Section", they are taken to a "Section Landing Page".

    In the main area, is a box with select "Sub-Sections" - each containing links to related Articles. (Dimension = "Featured_<section>")

    There would also be a way for the user to click on a "View all <Sub-Section> Articles", and be taken to a "Sub-Section Landing Page" which would provide a listing of Article Summaries related to the chosen "Sub-Section".

    5.) In the right margin of the "Section Landing Page", display various boxes of different "Dimensions" containing the related "Sub-Sections".

    If a user clicked on one of these secondary or tertiary SubSections, then they would be taken to a "Sub-Section Landing Page" which would provide a listing of Article Summaries related to the chosen "Sub-Section".

    6.) This would allow people choosing a "Section" to first and foremost see what I deem to be the most important Articles related to a given Section.

    However, it would also allow me to "slice and dice" a Section into many other facets that otherwise would get buried in a DEEP design.

    7.) The concept of a "Dimension" exists on the back-end to keep Articles properly organized, and it also exists *subtly* on the website itself.

    But as far as the URL and users are concerned, there is only the concept of...

    Section > SubSection > Article





    Anyways, in the next post, I will give you lots of sample data to help "visualize" things...

    Sincerely,


    Debbie

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,515
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    This all makes tons of sense. But it's still not clear to me why the DIMENSION has to be anything but a way to *find* the appropriate subsections for the given section. Meaning that it seems kind of like an auxiliary table, not necessarily part of the main structure.

    But I'll await your examples.
    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,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Yes, some sample data would help a lot.

    The below is *sample data*, but should provide a fairly accurate real-life example...


    SECTION
    Code:
    Finance
    Legal
    Management
    Operations
    Marketing

    SECTION_DIMENSION
    Code:
    Section		Dimension
    --------	----------
    Finance		Business Structure
    "		Store Type
    "		Offering
    "		Accounting
    "		Featured_Finance
    Legal		Business Structure
    "		Store Type
    "		Offering
    "		Featured_Legal

    DIMENSION
    Code:
    Business Structure
    Store Type
    Offering
    Accounting
    Featured_Finance
    Featured_Legal

    DIMENSION_SUBSECTION
    Code:
    Dimension		SubSection
    ----------		-----------
    Business Structure	Sole Proprietorship
    "			Partnership
    "			LLC
    "			S-Corp
    "			C-Corp
    Store Type		Brick & Mortar
    "			Online
    "			Hybrid
    Offering		Products
    "			Services
    "			Full Service
    Accounting		Bookkeeping
    "			Payroll
    "			Taxes
    Featured_Finance	Economy
    "			Investing
    "			Markets
    Featured_Legal		General Counsel
    "			Copyrights & Patents
    "			Tax Law
    "			Litigation

    SUBSECTION
    Code:
    Sole Proprietorship
    Partnership
    LLC
    S-Corp
    C-Corp
    Brick & Mortar
    Online
    Hybrid
    Products
    Services
    Full Service
    Bookkeeping
    Payroll
    Taxes
    Economy
    Investing
    Markets
    General Counsel
    Copyrights & Patents
    Tax Law
    Litigation

    SECTION_DIMENSION_SUBSECTION_ARTICLE
    (Below I am showing every Section-Dimension-SubSection combination, but also showing one Article that would be mapped to 3 different Section-Dimension-SubSection combinations...)
    Code:
    SECTION		DIMENSION		SUBSECTION		ARTICLE
    --------	----------		-----------		--------
    Finance		Business Structure	Sole Proprietorship
    "		"			Partnership
    "		"			LLC
    "		"			S-Corp
    "		"			C-Corp
    "		Store Type		Brick & Mortar
    "		"			Online			BeSureToChargeSalesTax.php
    "		"			Hybrid
    "		Offering		Products
    "		"			Services
    "		"			Full Service
    "		Accounting		Bookkeeping
    "		"			Payroll
    "		"			Taxes			BeSureToChargeSalesTax.php
    Legal		Business Structure	Sole Proprietorship
    "		"			Partnership
    "		"			LLC
    "		"			S-Corp
    "		"			C-Corp
    "		Store Type		Brick & Mortar
    "		"			Online
    "		"			Hybrid
    "		Offering		Products
    "		"			Services
    "		"			Full Service
    "		Featured Legal		General Counsel
    "		"			Copyrights & Patents
    "		"			Tax Law			BeSureToChargeSalesTax.php
    "		"			Litigation

    Hope that helps?!

    Sincerely,


    Debbie

  • #6
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    This all makes tons of sense.
    I'm glad it does to somebody?!


    Quote Originally Posted by Old Pedant View Post
    But it's still not clear to me why the DIMENSION has to be anything but a way to *find* the appropriate subsections for the given section. Meaning that it seems kind of like an auxiliary table, not necessarily part of the main structure.

    But I'll await your examples.
    I have thought that same thing, HOWEVER, remember these "Business Rules"... (Actually not stated as such, above, but rather as "Observations"?!)

    Observations:
    1.) Having SECTION_DIMENSION is good, because it defines which combinations of Sections and Dimensions can exist.

    (Remember, this can't be a "free-for-all"... You couldn't have the dimension "Featured_Legal" mapping to "Finance")


    2.) Having DIMENSION_SUBSECTION is good, because it defines which combinations of Dimensions and Sub-Sections can exist.

    (Again, this can't be a "free-for-all"... You couldn't have the sub-section "Payroll" mapping to the dimension "Business-Structure".)
    Sincerely,


    Debbie

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,515
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    You missed showing the SUBSECTION_ARTICLE table. <grin/> But I think that's pretty well implied by everything else.

    I don't think you changed my thinking much.

    The truly important table here really is that SUBSECTION_ARTICLE table, no?
    The other tables are just a way to navigate from SECTION down to an appropriate collection of SUBSECTIONs.

    And that means that everything you have said and done indeed makes sense.

    Sure, you *COULD* have done this without the DIMENSIONs. In essence, all you'd do would be to rename your subsections:
    DIMENSION Featured Legal + SUBSECTION Tax Law
    would convert to
    DIMENSIONSUBSECTION Featured Legal Tax Law

    Or you could have done it with a single table. The one you showed, just above, SECTION_DIMENSION_SUBSECTION_ARTICLE. If you wanted, you truly could implement it like that, perhaps using ENUM values for the Dimension and Sub-section fields.

    So to me this isn't really a question about database organization, per se, as just a way for you to make it easier for you to VIEW the organization.

    If I'm wrong, then your answer to the following question would have to be "yes":

    Question: Would there ever be a case where there would be an ARTICLE in a given SUBSECTION that would need to be *excluded* because of the SECTION it is in?

    It sure looks to me like the answer to that is "no." In which case I think you are 100% on the right track.
    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.

  • #8
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Old Pedant,

    I get kicked out of the library in 15 minutes, so I might not be able to respond *properly* until after supper later tonight.

    (Please don't leave the country on me!!!)

    Thanks,


    Debbie

  • #9
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Okay, I'm back. (Ugh, I ate too much, too late?! Let's hope I can respond to this critical post in an intelligent way...)


    Quote Originally Posted by Old Pedant View Post
    You missed showing the SUBSECTION_ARTICLE table. <grin/> But I think that's pretty well implied by everything else.
    No, I just wasn't sure if I needed that or something else.


    I don't think you changed my thinking much.

    The truly important table here really is that SUBSECTION_ARTICLE table, no?

    The other tables are just a way to navigate from SECTION down to an appropriate collection of SUBSECTIONs.
    Yes and No.

    On one hand, an Article only maps to a Sub-Section, because a Sub-Section is where the Article Link and Article are ultimately displayed.

    But on the other hand, the way a user accesses an Article, is by going to a URL like this...
    Code:
    www.debbie.com/finance/online/be-sure-to-charge-sales-tax
    In this situation, my script will...

    1.) Check for a valid "Section"

    2.) Check for a valid "Sub-Section"

    3.) And/or check that "finance" and "online" are a valid combination in some table (e.g. SECTION_DIMENSION_SUBSECTION) or in some query.

    Regardless, I will need the SECTION table properly joined to the DIMENSION table properly joined to the SUBSECTION table to validate part of the URL above.

    4.) Lastly, I will need to validate that an Article can be found using the "section_slug" and "dimension_slug" and "article_slug" from the above URL.

    My point?

    While an ARTICLE only directly maps to a SUBSECTION, it is the valid combination of a "Section" and "SubSection" - via a "Dimension" - and "Article" that allows an ARTICLE to be served up.

    Follow me?


    And that means that everything you have said and done indeed makes sense.

    Sure, you *COULD* have done this without the DIMENSIONs. In essence, all you'd do would be to rename your subsections:
    DIMENSION Featured Legal + SUBSECTION Tax Law
    would convert to
    DIMENSIONSUBSECTION Featured Legal Tax Law

    Here is the problem....

    Current Relationships:
    Code:
    SECTION -||-------|<- SECTION_DIMENSION ->|---------||- DIMENSION
    
    DIMENSION -||------|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION
    
    SUBSECTION ->|------||- ARTICLE

    There is no way to produce the "section_slug", "subsection_slug", and "article_slug" in one query from the above Tables and Relationships...


    I could create a new SECTION_SUBSECTION table, but that would bypass any Business Rules that I have in the SECTION_DIMENSION and DIMENSION_SUBSECTION junction tables...

    I could create a new SECTION_DIMENSION_SUBSECTION table, but that too would by-pass any Business Rules that I have in the SECTION_DIMENSION and DIMENSION_SUBSECTION junction tables...


    And I could get rid of the SECTION_DIMENSION and DIMENSION_SUBSECTION tables, and instead create a new SECTION_DIMENSION_SUBSECTION table, but that is getting larger, less manageable, and technically more de-normalized, right?

    Plus, that still wouldn't account for the relationship between a SUBSECTION and ARTICLE?!

    So maybe then I'd get rid of SECTION_DIMENSION and DIMENSION_SUBSECTION, and the new SECTION_DIMENSION_SUBSECTION table, plus the SUBSECTION_ARTICLE table, and just create a monster SECTION_DIMENSION_SUBSECTION_ARTICLE table, and treat the other tables as "feeder", "lookup" tables?!

    But that doesn't seem right...

    Following me?


    Or you could have done it with a single table. The one you showed, just above, SECTION_DIMENSION_SUBSECTION_ARTICLE. If you wanted, you truly could implement it like that, perhaps using ENUM values for the Dimension and Sub-section fields.
    In other words, remove the "Natural Keys" - the motivation for this thread - and just use "Derived Keys" to make the table more streamlined?


    So to me this isn't really a question about database organization, per se, as just a way for you to make it easier for you to VIEW the organization.
    I dunno. I was feeling like I just about had this before I left the library, but now I feel like I'm stuck back where I was when I started this thread...


    If I'm wrong, then your answer to the following question would have to be "yes":

    Question: Would there ever be a case where there would be an ARTICLE in a given SUBSECTION that would need to be *excluded* because of the SECTION it is in?
    If I understand the question...

    No, and ARTICLE and SUBSECTION(S) are "tightly-bound" so the SECTION wouldn't directly impact the relationship.

    However, an ARTICLE would be excluded from a SUBSECTION if it had nothing to do with the SUBSECTION and thus SECTION.

    "how-to-create-a-great-marketing-campaign.php" would not be in any of these DIMENSIONS: Business-Structure, Store-Type, Offering, Accounting, or Featured-Legal

    Nor would it be in either of these SECTIONS: Finance, Legal

    Why?

    Because that ARTICLE has no logical relationship to either of those SECTIONS or DIMENSIONS.

    However, it would be in a Sub-Section like "advertising" in a Section like "Marketing".


    It sure looks to me like the answer to that is "no." In which case I think you are 100% on the right track.
    I'll see what you think after reading my comments and concerns above.

    Thanks for all of your help so far!!

    Sincerely,


    Debbie

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,515
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    Okay, bear with me. I'm going to try to see where you are seeing the problem(s).

    I created some tables:
    Code:
    mysql> select * from section_dimension;
    +---------+----------------+
    | section | dimension      |
    +---------+----------------+
    | Finance | Offering       |
    | Legal   | Offering       |
    | Legal   | Featured_Legal |
    +---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from dimension_subsection;
    +----------------+------------+
    | dimension      | subsection |
    +----------------+------------+
    | Offering       | Services   |
    | Featured_Legal | Tax Law    |
    +----------------+------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from subsections;
    +------------+-------------------------------+
    | subsection | article                       |
    +------------+-------------------------------+
    | Services   | Pricing professional services |
    | Tax Law    | Be sure to charge sales tax   |
    +------------+-------------------------------+
    2 rows in set (0.02 sec)
    And then I can do this:
    Code:
    mysql> select SD.*, SS.*
        -> from section_dimension AS SD, dimension_subsection AS DS, subsections AS SS
        -> where SD.dimension = DS.dimension
        -> and DS.subsection = SS.subsection;
    +---------+----------------+------------+-------------------------------+
    | section | dimension      | subsection | article                       |
    +---------+----------------+------------+-------------------------------+
    | Finance | Offering       | Services   | Pricing professional services |
    | Legal   | Offering       | Services   | Pricing professional services |
    | Legal   | Featured_Legal | Tax Law    | Be sure to charge sales tax   |
    +---------+----------------+------------+-------------------------------+
    3 rows in set (0.02 sec)
    to get your master list of all combinations. (A small list, because I don't have much data in my tables, but it looks like a valid list.)

    And now what happens if the URLs are (examples)
    Code:
    debbie.com/finance/services
    debbie.com/legal/services
    debbie.com/legal/featured_legal
    debbie.com/finance/featured_legal
    I will presume that you are going to use rewrite rules to convert those urls into
    Code:
    debbie.com/showarticles.php?s=finance&ss=services
    debbie.com/showarticles.php?s=legal&ss=services
    debbie.com/showarticles.php?s=legal&ss=featured_legal
    debbie.com/showarticles.php?s=legal&ss=featured_legal
    or similar.

    And so then then PHP code (remembering that I don't use PHP, please) becomes:
    Code:
    <?php
    ...
    $section = $_GET["s"];
    $subsection = $_GET["ss"];
    $sql = "SELECT SS.article " 
         . " from section_dimension AS SD, dimension_subsection AS DS, subsections AS SS"
         . " where SD.dimension = DS.dimension"
         . " and DS.subsection = SS.subsection"
         . " and SD.section = '$section' "
         . " and SS.subsection = '$subsecton' ";
    $result = mysql_query( $sql );
    if ( ! $result ) 
    {
        echo "There are no articles for that section and subsection";
    } else {
        ... and then dump out the list of articles that is returned ...
    }
    And, indeed, for the first three URLs given there, I will get one article each.
    For the last URL, with the invalid combination of section and subsection, I get the "no articles" message.

    SO...

    Where is the problem in all of that?

    Am I just being way too simplistic????
    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.

  • Users who have thanked Old Pedant for this post:

    doubledee (05-14-2013)

  • #11
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Old Pedant,

    Wow, intense post!! (You seem to have answers for everything?!)

    So, I read what you had to say, and "Yes", it sure seems that you were able to get things to work, however I have to take a long hard look at what you did and *digest* all of this?!

    Here are two big areas where I have questions on your latest post...

    1.) I was somewhat unfamiliar with your SQL. I tend to be anal-retentive, and always use INNER JOINs.

    This is how I would code things...

    Code:
    SELECT sd.section, sd.dimemsion, ss.subsection, ss.article
    FROM section_dimension AS sd
    INNER JOIN dimension_subsection AS ds
    ON sd.dimension = ds.dimension
    INNER JOIN subsection AS ss
    ON ds.subsection = ss.subsection
    Would that also work?
    (Yes, I'll test it out. But you'll likely respond before I can.)

    Can you explain the difference between our SQL?


    2.) I feel kind of dumb right now, but for some unknown reason, I thought that I had to *physically* link together SECTION_DIMENSION and DIMENSION_SUBSECTION in order for things to work?!

    I guess when I look at the ERD I drew out, I technically do have all of my Tables from "Section" through "Article" daisy-chained together. (Just not in a way I am used to?!)


    So that is why I was freaking out, thinking that I would need to create more tables like SECTION_DIMENSION_SUBSECTION and SECTION_DIMENSION_SUBSECTION_ARTICLE.

    See my (wrong) thinking?


    Quote Originally Posted by Old Pedant View Post
    Okay, bear with me. I'm going to try to see where you are seeing the problem(s).

    I created some tables:
    [code]
    mysql> select * from section_dimension;
    +---------+----------------+
    | section | dimension |
    +---------+----------------+
    | Finance | Offering |
    | Legal | Offering |
    | Legal | Featured_Legal |
    +---------+----------------+
    3 rows in set (0.00 sec)
    How do you get those nifty Text Tables? (I hope you aren't typing those out by hand?!)

    They sure made your response 100 times easier to follow!!


    And now what happens if the URLs are (examples)
    Code:
    debbie.com/finance/services
    debbie.com/legal/services
    debbie.com/legal/featured_legal
    debbie.com/finance/featured_legal
    I will presume that you are going to use rewrite rules to convert those urls into
    Code:
    debbie.com/showarticles.php?s=finance&ss=services
    debbie.com/showarticles.php?s=legal&ss=services
    debbie.com/showarticles.php?s=legal&ss=featured_legal
    debbie.com/showarticles.php?s=legal&ss=featured_legal
    or similar.
    Yes, a big part of all of this is that I am using "Pretty URLs", and so I use mod_rewrites to transform a user-friendly URL for the user into something my PHP can use to find the Article.


    And, indeed, for the first three URLs given there, I will get one article each.
    For the last URL, with the invalid combination of section and subsection, I get the "no articles" message.

    SO...

    Where is the problem in all of that?

    Am I just being way too simplistic????
    I think you may have nailed it, but my tiny 486-processor of a brain needs time to process all of this.

    Answering my latest two questions above, will definitely close-the-gap, though!!

    In the mean time, I am going to try and reproduce your example above in my actual database. (This is sort of a pain, since I already laid out more realistic tables in MySQL. Either I create simple test tables like you used, or I may be bold and see if I can recreate a similar result with my actual table structure and data?!)

    You've been an enormous help so far, and I appreciate your patience with me. (It's always easier to learn when people aren't putting you down, which recently happened with someone...)

    Sincerely,


    Debbie

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,515
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    Quote Originally Posted by doubledee View Post
    ...
    1.) I was somewhat unfamiliar with your SQL. I tend to be anal-retentive, and always use INNER JOINs.
    ...
    Can you explain the difference between our SQL?
    No difference. I used what I call "implicit inner joins". It's actually an older style of inner join, before the INNER JOIN and LEFT JOIN and RIGHT JOIN keywords were introduced into SQL. But it still works just fine in all databases. It can only be used with inner joins; you still have to specify LEFT JOIN and RIGHT JOIN for outer joins. Use whichever you prefer.

    2.) I feel kind of dumb right now, but for some unknown reason, I thought that I had to *physically* link together SECTION_DIMENSION and DIMENSION_SUBSECTION in order for things to work?
    Not sure what you mean by physically. You mean specify FOREIGN KEYs? In theory, yes. And certainly if you want MySQL to enforce the referential integrity (and/or cascade deletes or updates, as you pointed out). But there's no *requirement* that you do so. (And I'm curious: How did you know I didn't do that?)

    So that is why I was freaking out, thinking that I would need to create more tables like SECTION_DIMENSION_SUBSECTION and SECTION_DIMENSION_SUBSECTION_ARTICLE.
    See, now, I never picked up on your thinking you needed to do that! No, you most certainly don't need to do so, as you can see.

    How do you get those nifty Text Tables? (I hope you aren't typing those out by hand?!)
    I'm just using the MySQL command line client. I'm not using any database "tool". You can do it, too. Just issue the command
    Code:
    mysql -uUSERNAME -pPASSWORD DATABASENAME
    from the windows DOS command prompt (or from a Linux prompt, if using Linux). That is, bring up a "Command Prompt" window and then type that in, using your actual USERNAME, PASSWORD, and DATABASENAME of course. It *may* not work, depending on how you installed MySQL. If not, you just have to find out where MySQL was installed, move to that directory, and then issue the command.

    But of course it means that you now have zero help from any database tool. You have to type in everything yourself.

    Want to see how I added the data to the tables? Example:
    Code:
    insert into section_dimension 
    values('Finance','Offering'),('Legal','Offering'),('Legal','Featured_Legal');
    
    insert into subsections 
    values('Services','Pricing professional services'),
    ('Tax Law','Be sure to charge sales tax');
    and so on. Lots of typing.
    In the mean time, I am going to try and reproduce your example above in my actual database. (This is sort of a pain, since I already laid out more realistic tables in MySQL. Either I create simple test tables like you used,
    Oh, don't do that.
    or I may be bold and see if I can recreate a similar result with my actual table structure and data?!
    You should be able to. It's just the JOINs that are the important part. Good luck.

    You've been an enormous help so far, and I appreciate your patience with me. (It's always easier to learn when people aren't putting you down, which recently happened with someone...)
    Well, you clearly had a good idea and ran with it. Even if you missed some points along the way, you pushed your own envelope. As I said in a prior post, I think you really did come up with at least one of the best ways to represent your organization of the data. I only get tired of people who want an instant answer without doing any thinking on their own. You're the opposite of that case. You want to understand. A breath of fresh air.
    Last edited by Old Pedant; 05-07-2013 at 07:51 PM.
    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.

  • Users who have thanked Old Pedant for this post:

    doubledee (05-14-2013)

  • #13
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    "And here comes Debbie, several lengths behind that slow, old tortoise..."




    Quote Originally Posted by Old Pedant View Post
    No difference. I used what I call "implicit inner joins". It's actually an older style of inner join, before the INNER JOIN and LEFT JOIN and RIGHT JOIN keywords were introduced into SQL. But it still works just fine in all databases. It can only be used with inner joins; you still have to specify LEFT JOIN and RIGHT JOIN for outer joins. Use whichever you prefer.
    Cool.


    Not sure what you mean by physically. You mean specify FOREIGN KEYs? In theory, yes. And certainly if you want MySQL to enforce the referential integrity (and/or cascade deletes or updates, as you pointed out). But there's no *requirement* that you do so. (And I'm curious: How did you know I didn't do that?)

    Okay, this is the hurdle I still need to get over.


    In phpMyAdmin, for the SECTION_DIMENSION table, I have one FK (section_id) pointing to (Table/Field) section.id, and then another FK (dimension_id) pointing to dimension.id.

    Then for the DIMENSION_SUBSECTION table, I have a FK (dimension_id) pointing to dimension.id, and then another FK (subsection_id) pointing to subsection.id.

    So, the 1st FK ties SECTION_DIMENSION to SECTION and the 2nd FK ties SECTION_DIMENSION to DIMENSION, right?

    And then the 3rd FK ties DIMENSION_SUBSECTION to DIMENSION and the 4th FK ties DIMENSION_SUBSECTION to SUBSECTION, right?

    However, your query was joining SECTION_DIMENSION and DIMENSION_SUBSECTION together...
    Code:
    mysql> select SD.*, SS.*
        -> from section_dimension AS SD, dimension_subsection AS DS, subsections AS SS
        -> where SD.dimension = DS.dimension
        -> and DS.subsection = SS.subsection;

    So I figured there needed to be something like this...
    Code:
    SECTION_DIMENSION -||------|<- SECTION_DIMENSION_SUBSECTION ->|-------||- DIMENSION_SUBSECTION
    ...where the "joins" would in essence be created when you created Foreign Key Constraints between the "Level 1 Junction Tables" and the new "Level 2 Junction Table".

    Follow my original mindset?


    But I believe this is how things actually work...


    Because I already have all of the Tables "joined" together like this...
    Code:
    SECTION -||----|<- SECTION_DIMENSION ->|------||- DIMENSION
    
    DIMENSION -||------|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION
    
    SUBSECTION -||------|<- ARTICLE
    ...there is NO NEED to create an *additional join* like this...
    Code:
    SECTION_DIMENSION -||------|<- SECTION_DIMENSION_SUBSECTION ->|-------||- DIMENSION_SUBSECTION

    Is that correct??


    And do you follow my original mindset??



    See, now, I never picked up on your thinking you needed to do that! No, you most certainly don't need to do so, as you can see.
    I think you are stating what I just tried to explain above, right?



    Quote Originally Posted by doubledee
    You've been an enormous help so far, and I appreciate your patience with me. (It's always easier to learn when people aren't putting you down, which recently happened with someone...)
    Well, you clearly had a good idea and ran with it. Even if you missed some points along the way, you pushed your own envelope. As I said in a prior post, I think you really did come up with at least one of the best ways to represent your organization of the data.
    THANKS!!! That really makes my day!!

    Counter to what this other person said - which was basically "Your schema is good for a classroom project, but will never scale in the real world" - I think my design is very scalable.

    If tomorrow, I want to add an "IT" section, then my model handles it. Or if I decide to start writing Articles by different sectors, then all I have to do is drop in an "Industry" dimension, and, of course, I can then add individual industries - in this case "Sub-Sections" - until the end of time!

    And, considering that I haven't written a single Article yet, this entire task of coming up with a realistic and flexible Data Model was hell!

    However, I think I have a fairly simple Data Model that will last me a long, long time. (And by the time I outgrow, I'll maybe have enough $$$ to pay a pro to do this for me?!)


    I only get tired of people who want an instant answer without doing any thinking on their own. You're the opposite of that case. You want to understand. A breath of fresh air.
    When I can find patient mentors, I am a very eager student!!

    I love discussing "theory" while solving problems, and trying to find the best way to do things, while also having alternatives in the background.

    This is especially so when it comes to Data Modeling, for which I have always had a special place in my heart! Because, it is the "data" that makes your system and business go round!!

    Sincerely,


    Debbie

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,515
    Thanks
    77
    Thanked 4,379 Times in 4,344 Posts
    Maybe this will help.
    Code:
    CREATE TABLE `sections` (
      `section` varchar(100) NOT NULL,
      PRIMARY KEY (`section`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
    
    mysql> select * from sections;
    +---------+
    | section |
    +---------+
    | Finance |
    | Legal   |
    +---------+
    
    CREATE TABLE `dimensions` (
      `dimension` varchar(100) NOT NULL,
      PRIMARY KEY (`dimension`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
    
    mysql> select * from dimensions;
    +----------------+
    | dimension      |
    +----------------+
    | Featured_Legal |
    | Offering       |
    +----------------+
    
    CREATE TABLE `section_dimension` (
      `section` varchar(100) DEFAULT NULL,
      `dimension` varchar(100) DEFAULT NULL,
      KEY `section` (`section`),
      KEY `dimension` (`dimension`),
      CONSTRAINT FOREIGN KEY (`dimension`) REFERENCES `dimensions` (`dimension`),
      CONSTRAINT FOREIGN KEY (`section`) REFERENCES `sections` (`section`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
    
    
    mysql> select * from section_dimension;
    +---------+----------------+
    | section | dimension      |
    +---------+----------------+
    | Finance | Offering       |
    | Legal   | Offering       |
    | Legal   | Featured_Legal |
    +---------+----------------+
    No IDs needed. As you had originally asked about, I am using just the VARCHAR fields themselves as both the primary and foreign keys.
    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.

  • #15
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,092
    Thanks
    26
    Thanked 0 Times in 0 Posts
    What is mixing me up, is this...

    When I create INNER JOINS in my queries, I am used to starting with a Parent Table (e.g. SECTION) and then joining the Child, Junction Table (e.g. SECTION_DIMENSION) and then doing another INNER JOIN back to the other Parent (e.g. DIMENSION).

    The point being that the "Junction Table" is what is causing the link between the Parent Tables - thus the name.

    However, with your query, you were going from Child, Junction Table (i.e. SECTION_DIMENSION) up to a Parent Table (i.e. DIMENSION) and then back down to another Child, Junction Table (i.e. DIMENSION_SUBSECTION).

    Because I am used to INNER JOINS consisting of joining Parent ---> Child <--- Parent, it threw me off?!


    So, to be clear, could you please answer this earlier question....

    But I believe this is how things actually work...


    Because I already have all of the Tables "joined" together like this...

    Code:
    SECTION -||----|<- SECTION_DIMENSION ->|------||- DIMENSION
    
    DIMENSION -||------|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION
    
    SUBSECTION -||------|<- ARTICLE
    ...there is NO NEED to create an *additional join* like this...

    Code:
    SECTION_DIMENSION -||------|<- SECTION_DIMENSION_SUBSECTION ->|-------||- DIMENSION_SUBSECTION

    Is that correct??


    Sincerely,


    Debbie


  •  
    Page 1 of 4 123 ... LastLast

    Posting Permissions

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