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 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 49
  1. #31
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Old Pedant,

    Okay, to show you that I am *really* trying to learn, here is what I just did...


    I created this modified ERD from your earlier advice...
    Code:
    SECTION -||-----|<- SECTION_SUBSECTION ->|---------||- SUBSECTION
    
    SECTION_SUBSECTION -||-----|<- ARTICLE_PLACEMENT ->|------||- ARTICLE

    And I create - using Natural Keys - this same data...

    Code:
    SECTION
    -------
    Legal
    
    
    SUBSECTION
    -----------
    Sole Proprietorship
    
    
    SECTION_SUBSECTION
    -------------------
    Legal, Sole Proprietorship
    
    
    ARTICLE_PLACEMENT
    ------------------
    Legal, Sole Proprietorship, WhenASoleProprietorshipGetsSued.php
    
    
    ARTICLE
    --------
    WhenASoleProprietorshipGetsSued.php

    Code:
    I also set up the requisite PK's, FK's and "CASCADE ON UPDATE" and "CASCADE ON DELETE".

    Test Steps:

    - I want to make my article broader, so I changed it to "WhenASmallBusinessGetsSued.php"

    - The change propagated to ARTICLE_PLACEMENT

    - All is well.

    - Sole Proprietorships just aren't working for me, so I decided to delete SubSection = "Sole Proprietorship"

    - The one entry in SECTION_SUBSECTION and ARTICLE_PLACEMENT got deleted.

    - All is well.

    - I want to deal with a new SubSection, so I added SubSection = "Partnership".

    - All is well.

    - I decide that a "Partnership" is related to "Legal", so I created an entry in SECTION_SUBSECTION = "Legal", "Partnership".

    - All is well.

    - I decided that my newly expanded article = "WhenASmallBusinessGetsSued.php" relates to "Legal" and "Partnerships", so I create a new entry in ARTICLE_PLACEMENT = "Legal", "Partnerships", "WhenASmallBusinessGetsSued.php"

    - All is well.


    So how does that look for a mini-test of using your suggestion, and also flowing out how I would Add and Remove things??

    (Seems like everything worked in that "Test Scenario"?!

    Sincerely,


    Debbie

    P.S. I still want my "Dimensions" in there, but I figured this was a good "mental exercise"...
    Last edited by doubledee; 05-09-2013 at 08:50 PM.

  2. #32
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Ummm...why would they put it in there if it was evil?

    Every major database system--and even Microsoft's "toy" Access database--provides it.

    I think maybe the only reason it has a bad rep in MySQL is because it isn't supported by idiotic MyISAM. So if you were depending on it working and then forgot and used MyISAM...*KABLOOEY*.
    Maybe because it is soooo easy to trash a whole bunch of data with the click of a button?!

    I dunno.

    I have just picked that up from others over time. (Maybe just like I have gotten that silly idea in my head that "Natural Keys" are evil?!)

    Sincerely,


    Debbie

  3. #33
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Looks pretty good to me.

    Yes, you can trash a bunch of data with the click of a button.

    But you can do that, anyway, without CASCADE DELETE.

    Seems a specious argument, to me, by insecure coders.
    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.

  4. #34
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Looks pretty good to me.
    Yeah, thanks. (I'll use that as my "back-up plan" if I can't get the Dimensions to work out!)


    Yes, you can trash a bunch of data with the click of a button.

    But you can do that, anyway, without CASCADE DELETE.

    Seems a specious argument, to me, by insecure coders.
    True.


    Like most things, I'm sure there are valid arguments for and against this, but for my database, they are probably more good than bad.

    Sincerely,


    Debbie

  5. #35
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Is there some way that I can join together these two tables, without a Junction Table...

    Code:
    SECTION_DIMENSION
    - section
    - dimension
    
    
    DIMENSION_SUBSECTION
    - dimension
    - subsection

    I'm trying to find a way to grab the "Section" from SECTION_DIMENSION, and "Dimension" from DIMENSION_SUBSECTION, so I can combine those with "Article" from ARTICLE for the junction table ARTICLE_PLACEMENT.

    Hope that makes sense?!

    Sincerely,


    Debbie

  6. #36
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Actually, maybe there is another way...

    In my ERD, I have these joins...
    Code:
    SECTION -||------|<- SECTION_DIMENSION ->|------||-DIMENSION
    
    DIMENSION -||----|<- DIMENSION_SUBSECTION ->|-----||- SUBSECTION

    If I am not mistaken, there is a "Transitive Property of Equality" between SECTION_DIMENSION and DIMENSION_SUBSECTION because they share the common key of "Dimension", right??

    So, that should mean that if there is a "Section" in the first junction table, then the corresponding "SubSection" in the second junction table would be a correct pairing via the "Dimension" key, right?


    Following this thought-process, maybe I could do this...
    Code:
    SECTION_DIMENSION -||-----|<- ARTICLE_PLACEMENT
    
    DIMENSION_SUBSECTION -||-----|<- ARTICLE_PLACEMENT
    
    ARTICLE -||-------|<- ARTICLE_PLACEMENT

    Where my Foreign Keys would be....
    Code:
    article_placement.section
    
    article_placement.subsection
    
    article_placement.article

    And the actual PK/FK mapping would be...
    Code:
    SECTION_DIMENSION.section -||----|<- ARTICLE_PLACEMENT.section
    
    DIMENSION_SUBSECTION.subsection -||---------|<- ARTICLE_PLACEMENT.subsection
    
    ARTICLE.article -||------|<- ARTICLE_PLACEMENT.article

    How does that sound? (I'm trying to build this now...)

    Sincerely,


    Debbie
    Last edited by doubledee; 05-10-2013 at 12:23 AM.

  7. #37
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Ummm...from post #26:
    Code:
    CREATE TABLE article_placement (
        article VARCHAR(100),
        section VARCHAR(100),
        subsection VARCHAR(100),
        CONSTRAINT FOREIGN KEY article REFERENCES articles(article).
        CONSTRAINT FOREIGN KEY section REFERENCES sections(section).
        CONSTRAINT FOREIGN KEY subsection REFERENCES subsections(subsection).
    );
    Isn't that what I said? Really? Not quite sure what you are saying that is different.
    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. #38
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Ummm...from post #26:

    Isn't that what I said? Really? Not quite sure what you are saying that is different.
    I just finished updating post #36, so please check that out.


    And, no, what you mentioned above in Post #26 is different.

    You created a SECTION_SUBSECTION which doesn't work in the paradigm I want, because it completely bypasses the notion of a "Dimension". And thus, you could create a Section/SubSection pair like Finance/Litigation which would make no sense?!

    (You can see how I fixed that issue in Post #31 without having a "Dimension" table.)


    But since I want to keep my DIMENSION, I'm pretty sure that I am on to something in my last post (Post #36), however, please tell me what you think...

    Sincerely,


    Debbie

  9. #39
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Okay, makes sense. Proof is in the putting. [sic]
    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. #40
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Old Pedant,

    So I think I figured my Data Model out last night.

    But before discussing it, I'm not entirely happy with my options for Primary and Foreign Keys.

    Now don't hate me for asking this, but what if I wanted to do this...

    I really like the idea of having a unique "id" for every record of any given table. Doing so makes it extremely easy in both conversation and queries to refer to a record. It also provides a nice "time-stamp" of sorts to show the order that records were created.

    At the same time, I see the benefits of using "Natural Keys".

    In the Data Model we have been discussing, I am okay with using just "Natural Keys" for my Look-up Tables, but here is where I have a problem with them...

    In my "Article" table, I could easily use the "slug" field to serve as my PK, but I think it would be awkward at best, to have a table where the only way to refer to records is by referring to them by these names...

    Code:
    why-you-should-consider-incorporating-your-small-business.php
    
    do-i-want-an-llc-or-should-i-incorporate.php
    
    govt-cracks-down-on-etailers-and-sales-tax-online.php
    
    be-sure-your-1099-contractors-pay-their-own-payroll-taxes.php

    Especially since this table could become tens of thousands of records, it would be so much easier to refer to a record as "ID=8391" versus "SLUG='be-sure-your-1099-contractors-pay-their-own-payroll-taxes.php'"


    So here is what I was thinking...

    - Create an "id" field, make it an AutoIncrement, and a PK.

    - Then add a "Unique Index" (Constraint) to the "slug" field.

    - Then when I do joins, I could use the "slug" field - since it is unique - and still get the benefits of a "Natural Key" while also having my "id" field.


    Is that a good or bad idea?

    Sincerely,


    Debbie

  11. #41
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Only minor change I would make: Leave the "slug" field as the PK and instead make the UNIQUE INDEX on the auto_increment field.

    Yes, that's legal:
    Code:
    create table asdf ( 
        id int auto_increment, 
        zam varchar(100) primary key, 
        unique key (id) 
    ) engine innodb;
    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.

  12. Users who have thanked Old Pedant for this post:

    doubledee (05-14-2013)

  13. #42
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Only minor change I would make: Leave the "slug" field as the PK and instead make the UNIQUE INDEX on the auto_increment field.

    Yes, that's legal:
    Code:
    create table asdf ( 
        id int auto_increment, 
        zam varchar(100) primary key, 
        unique key (id) 
    ) engine innodb;
    You're my hero!!!

    How come when I tried that 6 months ago, it didn't work in phpMyAdmin?!

    Is it maybe because I didn't put a "Unique Index" on the AutoIncrement field??


    Questions:

    1.) Does this new approach meet your approval?

    2.) If - for whatever reason - I wanted/needed to do a join between tables using a MySQL "Unique Index" field, is that acceptable? (Obviously it is better if it is a true "PK", but if you had to...)


    If using the approach above doesn't violate any "Rules of Good DB Design", then I think it makes me more inclined to use "Natural Keys".

    Because after some thought, I feel that "Every record in a given table should have a Numeric ID which can easily identify it."

    And another benefit of what you showed me above is that if I ever wanted/needed to switch the PK from a "Natural Key" to a "Derived Key", then I have *both* right there!!

    What do you think about all of that?!

    Sincerely,


    Debbie
    Last edited by doubledee; 05-10-2013 at 08:27 PM.

  14. #43
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Quote Originally Posted by doubledee View Post
    How come when I tried that 6 months ago, it didn't work in phpMyAdmin?!
    Is it maybe because I didn't put a "Unique Index" on the AutoIncrement field??
    Yes. An auto_increment field *MUST* get a unique index. Naturally making it the primary key is the easiest way of doing that, but that's not required. Any unique key (including even a composite key, by the by) works.

    1.) Does this new approach meet your approval?
    Shoot, most everything you have done "meets my approval". When I've made suggestions, they've been alternatives, not corrections, in most if not all cases.
    2.) If - for whatever reason - I wanted/needed to do a join between tables using a MySQL "Unique Index" field, is that acceptable? (Obviously it is better if it is a true "PK", but if you had to...)
    Yes, but there are good and solid performance reasons not to do so under most circumstances.
    If using the approach above doesn't violate any "Rules of Good DB Design", then I think it makes me more inclined to use "Natural Keys".

    Because after some thought, I feel that "Every record in a given table should have a Numeric ID which can easily identify it."
    I'm not at all convinced of your last sentence. You may have fine reasons for believing that in this specific case, well and good, but there are too many other cases where I think that isn't true. But that's quibbling.
    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. #44
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Old Pedant,

    So I think I finally nailed the Data Model for my "new & improved" Articles section!

    Here is the ERD I am going with...

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

    For each table, the 1st column is "id" with a Unique Index and AutoIncrement.

    The 2nd column is "slug" as serves are the Primary Key.

    Here are some examples...

    Code:
    SECTION
    - id (UK)
    - slug (PK)
    - name
    - created_on
    - updated_on
    
    
    SECTION_DIMENSION
    - id (UK)
    - section_slug (PK1)(FK)
    - dimension_slug (PK2)(FK)
    - created_on
    - updated_on
    
    
    ARTICLE_PLACEMENT
    - id (UK)
    - sd_section_slug (PK1)(FK)
    - ds_subsection_slug (PK2)(FK)
    - article_slug (PK3)(FK)
    - created_on
    - updated_on

    And here is some sample data from my ARTICLE_PLACEMENT table...

    Code:
    (UK)	(PK1)(FK)		(PK2)(FK)		(PK3)(FK)
    id	sd_section_slug		ds_subsection_slug	article_slug
    --	---------------		------------------	------------
    1	finance			economy			which-states-support-main-street
    2	finance			economy			sequester-could-lead-to-more-start-ups
    3	finance			economy			fastest-growing-small-business-sectors-for-2013
    4	finance			economy			sec-silent-as-crowdfunders-wait
    5	finance			economy			best-cities-for-small-business
    6	finance			markets			us-stocks-decline-as-retail-sales-fall-commodities-tumble
    7	finance			markets			billionaire-paulson-loses-more-than-300-million-as-gold-falls
    8	finance			markets			asian-stocks-rise-most-in-7-months-on-chinese-inflation
    9	finance			markets			jc-penney-said-to-hire-blackstone-to-raise-1-billion
    10	finance			markets			brazil-seeks-higher-power-auction-rate-to-spur-use-of-coal
    Beautiful, isn't it?!


    Time will tell how all of this works out, but I think I have a rock-solid Data Model and Table Layout Strategy. And both should last me a long time.

    Also, since I insisted on having an "id" in every table, if I ever want to switch from "Natural Keys" to "Derived Keys", it will be fairly easy to do...

    Thanks for all of your help on this, Old Pedant!!!

    Sincerely,


    Debbie

  16. #45
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Old Pedant,

    With my Data Model seemingly done, I have started looking into what needs to be done to go from the user selecting a "Pretty URL", to retrieving data from MySQL, to displaying the requested Article.

    Here is one thing I have noticed that seems a little amiss... (This may be more of a mod_rewrite or PHP question.)


    Others have stressed to me that having a URL that is too long - even if it is "pretty" - is counter-productive. As such, I decided to drop "Dimension" from my Pretty URLs.

    So, let's say that a user clicks on an Article link in the "Tax Season" SubSection.

    Now remember that the "Tax Season" subsection could either be located in the center area of a given Section (i.e. Dimension = "Featured Finance"), or off in the right margin of a given Section (e.g. Dimension = "Seasonal").

    In either case - since "Dimension" doesn't exist in the URL - they would both be pointing to a URL like this...
    Code:
    www.debbie.com/finance/tax-season/save-your-taxes-for-a-cpa

    So, logically, each of the two different areas on the given Section page would map to two different records in the database.

    However, physically, only one URL (see above) would map to two different records in the database.

    Code:
    Section		Dimension		SubSection	Article
    --------	----------		-----------	--------
    Finance		Featured Finance	Tax Season	Save Your Taxes for a CPA
    
    Finance		Seasonal		Tax Season	Save Your Taxes for a CPA


    And now for my question(s)...

    When I use PHP to *sanitize* the submitted URL, how should I go about checking things?


    Off the top of my head, here are the general steps I'd need to take...

    1.) Use mod_rewrite to parse up the URL and assign different parts to variables (i.e. Section, SubSection, and Article).

    2.) Next, I would run this query...
    Code:
    SELECT s.name AS Section, d.name AS Dimension, ss.name AS SubSection, a.heading AS Article
    FROM section AS s
    INNER JOIN section_dimension AS sd
    ON s.slug = sd.section_slug
    INNER JOIN dimension AS d
    ON d.slug = sd.dimension_slug
    INNER JOIN dimension_subsection AS ds
    ON d.slug = ds.dimension_slug
    INNER JOIN subsection AS ss
    ON ss.slug = ds.subsection_slug
    INNER JOIN article_placement AS ap
    ON (sd.section_slug = ap.sd_section_slug)
    AND (ds.dimension_slug = ap.ds_dimension_slug)
    AND (ds.subsection_slug = ap.ds_subsection_slug)
    INNER JOIN article AS a
    ON a.slug = ap.article_slug
    WHERE s.name = 'Finance'
    AND ss.name = 'Tax Season'
    AND a.heading = 'Save Your Taxes For A CPA'
    ORDER BY s.sort, d.sort, ss.sort
    ...and make sure that it returns AT LEAST ONE RECORD.

    (*NOTE: Because I'm thorough, I would actually probably run Query #1 to check that there was a valid "Section", and then Query #2 to check for a valid "Section/SubSection" pair, and then Query #3 - which is the one above - to check the whole combo.

    Doing so would allow me to present more customized Error-Handling and Error-Logging...)

    3.) If there was at least one record, then I'd run a final query which would gather all of the needed columns to display the Article itself.

    Not sure if I could just query the ARTICLE table to get the Article, or if to be thorough I would want to run a query against all tables - like above - to make sure that the "Section", "SubSection" and "Article" all match?? (Security is *really* important to me, so I get pretty neurotic when it comes to checking that data submitted by the user is "clean"!!!)

    (If the latter, then I'd need to add a column to ARTICLE_PLACEMENT called something like "placement_primary", so that I would know which record to pull in case the Article exists in multiple forms in my ARTICLE_PLACEMENT table, if you follow that?!)


    How does all of that sound??

    Sincerely,


    Debbie
    Last edited by doubledee; 05-15-2013 at 10:49 PM.


 
Page 3 of 4 FirstFirst 1234 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
  •