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 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 49
  1. #16
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Yes, on all counts.

    Although you would normally JOIN back to the SECTIONS and DIMENSIONS tables if you were using IDs as your primary and foreign keys, one of the HUGE advantages of using NATURAL KEYS (remember, that's where we started? <grin/>) is that you don't *HAVE* to do that.

    Let's simplify:
    Code:
    SELECT sections.section, section_dimension.section
    FROM sections INNER JOIN section_dimension
    ON sections.section = section_dimension.section
    That works, but isn't it pretty silly?

    You just get the same section value twice in each record.

    So why not simply
    Code:
    SELECT section_dimension.section
    FROM section_dimension
    ???? It gets you the same list of section values as the unneeded inner join gets you.

    So extend the logic: When joining to section_dimension from other tables, there is *STILL* no reason to join to sections if all you need is the section name.

    Once again, this is all a nice fall-out from choosing to use natural keys.

    Play with it some more if you aren't convinced.
    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.

  2. #17
    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
    Yes, on all counts.
    So, it looks like I am "on track", right?

    And it looks like if I follow through on your advice from this morning, then I am not only in good shape as far as my Data Model, but also how I will take that and use it with PHP and my mod_rewrites, right?


    Although you would normally JOIN back to the SECTIONS and DIMENSIONS tables if you were using IDs as your primary and foreign keys, one of the HUGE advantages of using NATURAL KEYS (remember, that's where we started? <grin/>) is that you don't *HAVE* to do that.

    So extend the logic: When joining to section_dimension from other tables, there is *STILL* no reason to join to sections if all you need is the section name.

    Once again, this is all a nice fall-out from choosing to use natural keys.

    Play with it some more if you aren't convinced.
    Actually, I already saw that, and when I was originally creating my monster SECTION_DIMENSIONS_SUBSECTION_ARTICLES table, I was attracted to that!!

    ********************************
    OFF TOPIC:

    So here is a "true confession"...

    One big reason I started asking about "Natural Keys" and was leaning towards creating a SECTION_DIMENSION_SUBSECTION_ARTICLE table, was because I don't have a convenient way to input data into my Junction Tables?!

    You see, in the past I just used phpMyAdmin. But if I have 4 tables, and my Junction Tables just consist of Integer values, then it would be a royal pain to add Articles to my database.

    And being the web-programming weenie that I am, I was fearful that creating a web-form to create Junction Tables would be too complex. (Since we started talking, I got some help on here, and now think building a form won't be quite as bad as I originally thought.)

    So THAT was the main motivation for dipping my foot in the "Natural Keys Pool"...

    ********************************

    Ironically, since we started talking, I can also see lots of benefits to sticking with "Derived Keys". For example, my Section, Dimension, and SubSection names could be more fluid than hoped. So the stability of an AutoIncrement Integer has value.

    I'll have to sleep on it...

    But in my mind, the more *important* things is knowing how to do things multiple ways...

    That is what will make me stronger and better than my competition!!


    And on that note...

    Can you please tell me the Pros & Cons of just making my SECTION, DIMENSION, SUBSECTION, and ARTICLE "pseudo" Look-up Tables, and having one monster SECTION_DIMENSION_SUBSECTION_ARTICLE Junction table??

    Especially if I used "Natural Keys", everything would be in one table for the reading?!

    I guess one downside would be space.

    And a larger issue would be that I would lose the ability to enforce Business Rules of which SubSections belong to which Dimensions, and can map to which Sections...


    Anyways, hopefully I am almost "over the hurdle" on this one entire thread/topic...

    Thanks for all of your help so far!!!

    Sincerely,


    Debbie

  3. #18
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    <shrug>Heck you could just put it all in one Excel spreadsheet and then connect to that and use it as a database. </shrug>

    But it doesn't scale well.

    I would stay where you are.

    And as for your comment about being able to change section/dimension/et al. names if you used AUTO_INCREMENT primary keys...

    Tch. You now do get a "shame on you". For forgetting that YOU were the one who originally mentioned ON UPDATE CASCADE.

    Look here:
    Code:
    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`) ON UPDATE CASCADE,
      CONSTRAINT FOREIGN KEY (`section`) REFERENCES `sections` (`section`) ON UPDATE CASCADE
    ) ENGINE=InnoDB
    
    mysql> select * from section_dimension;
    +---------+----------------+
    | section | dimension      |
    +---------+----------------+
    | Finance | Offering       |
    | Legal   | Offering       |
    | Legal   | Featured_Legal |
    +---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> update sections set section='Money, Money, Money' where section = 'Finance';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from section_dimension;
    +---------------------+----------------+
    | section             | dimension      |
    +---------------------+----------------+
    | Money, Money, Money | Offering       |
    | Legal               | Offering       |
    | Legal               | Featured_Legal |
    +---------------------+----------------+
    3 rows in set (0.00 sec)
    Hmmm??? What was that about "flexibility"??

    Notice that I did *NOT* manually change the value of section in the section_dimension table!!!
    MySQL changed it FOR ME, thanks to ON UPDATE CASCADE.

    Who needs those stinkin' auto_increment fields, anyway?
    Last edited by Old Pedant; 05-08-2013 at 01:35 AM.
    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. #19
    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 would stay where you are.
    Okay.


    And as for your comment about being able to change section/dimension/et al. names if you used AUTO_INCREMENT primary keys...

    Tch. You now do get a "shame on you". For forgetting that YOU were the one who originally mentioned ON UPDATE CASCADE.
    Gee, just when I was on a roll...


    I didn't forget, but it just seems that some people feel that using ON UPDATE CASCADE is "hokey"...


    Hmmm??? What was that about "flexibility"??

    Notice that I did *NOT* manually change the value of section in the section_dimension table!!!
    MySQL changed it FOR ME, thanks to ON UPDATE CASCADE.

    Who needs those stinkin' auto_increment fields, anyway?
    So it sounds like you are pretty strong in the "Natural Keys" group...

    In your eyes and experience, are there any compelling reasons to use "Derived Keys" over "Natural Keys"?


    I guess I can see pros and cons in both ways...

    Sincerely,


    Debbie

  5. #20
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Old Pedant,

    I think I am still stuck in the "deep grass"...

    If you would, please look at this sample set of data...
    Code:
    SECTION		DIMENSION		SUBSECTION		ARTICLE
    --------	----------		-----------		--------
    Finance		Business Structure	Sole Proprietorship
    "		"			Partnership
    "		"			LLC
    "		"			S-Corp			ConsiderBecomingAnS-Corp.php
    "		"			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			ConsiderBecomingAnS-Corp.php
    "		"			C-Corp
    "		Store Type		Brick & Mortar
    "		"			Online
    "		"			Hybrid
    "		Offering		Products
    "		"			Services
    "		"			Full Service
    "		Featured Legal		General Counsel
    "		"			Copyrights & Patents
    "		"			Tax Law			BeSureToChargeSalesTax.php
    "		"			Litigation

    Here are some issues that I discovered tonight...

    1.) Earlier in this thread, I somehow got things mixed up and said that "One SubSection can have one or more Articles".

    In reality, this should be a many-to-many relationship.

    Code:
    SUBSECTION -||------|<- SUBSECTION_ARTICLE ->|------||- ARTICLE

    2.) I am not so sure that I want to be locked into the idea that any given Article that maps to a given SubSection must maintain that relationship across Sections.

    I don't have any great examples of why you'd want this, but it seems like a *reasonable* assumption and expectation.

    Don't you agree?

    For example, in the sample data above, the article "BeSureToChargeSalesTax.php" appears under Finance > Store Type > Online, however I left it out of Legal > Store Type > Online, because it is already present under Legal > Featured Legal.

    Feel free to tell me what you think about this.

    However, if I follow that approach, then I fear this just trashed everything we worked out over the last two days?!


    3.) Related to #2, I am concerned about Data Entry, because in order for me to realistically determine which Sub-Sections I want to assign an Article to, I need to see "the big picture" (i.e. Section, Dimension, and Sub-Section) all at once.


    -------
    I dunno. My mind is really fried right now?!

    But this much I do know...

    I may be making my life (and yours) crazy right now, but I'd rather really think things out, and have a well-architected system that scales down the road, than something that is thrown together and which will freeze in a few months after I start going crazy adding lots of Articles!!!

    Not sure if you or anyone else sees it that way.

    Hopefully I haven't burned you out yet, and you can help me see this through to a successful completion...

    Think I better go get some sleep for now...

    Sincerely,


    Debbie

  6. #21
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Here is an example of what I am concerned about above...

    Let's say that I write the following new Article:
    Code:
    "WhenASoleProprietorGetsSued.php"

    This Article is about "Sole Proprietorships" (subsection) and it is about "Litigation" (subsection).

    So, you could argue that it should go here...

    Code:
    Finance > Business Structure > Sole Proprietorships
    
    Legal > Business Structure > Sole Proprietorships
    
    Legal > Featured Legal > Litigation

    But this Article really has nothing to do with "Finance", unless you are talking about getting funds to pay an attorney or a losing settlement!!

    So in this situation, I need a Data Model - and a Form to build my Junction Table(s) - that would allow me to assign the Article as follows...

    Code:
    Legal > Business Structure > Sole Proprietorships
    
    Legal > Featured Legal > Litigation

    Please note this is "subjective", but that is the whole point...

    A good Data Model should adjust to the Business, and not the other way around - which is all to often the case in my day job!! *sigh*

    Hopefully this *clarified* Requirement doesn't trash everything we've been working on?!

    Sincerely,


    Debbie

  7. #22
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    it sounds like you are pretty strong in the "Natural Keys" group...
    No. I am strongly in the camp of choosing the appropriate keys for the task at hand.

    In your eyes and experience, are there any compelling reasons to use "Derived Keys" over "Natural Keys"?
    Performance. CLEARLY if you have millions of records then an INT key is going to gain a *LOT* over a VARCHAR key. Simple as that.

    Even then, it depends on how hard you expect to hit the DB. That is, how many queries per minute or per second.

    But for only a few thousand records? Even tens of thousands? And on a website that gets no more than, say 10 hits per minute? You'll never ever be able to see the performance difference.

    I work on one site that gets over 300,000 database-related page hits per week. Say 48,000 on a typical day. That's 2,000 per hour, or more than one per second. And at least half of those queries (probably more like 75% of them) require a SQL WHERE clause that uses a KEY based on one or more VARCHAR columns. And we have essentially 100% uptime and very minimal response time.

    PICK YOUR BATTLES CAREFULLY. If performance is not an issue (and I can't imagine it is, on your site), then choose the solution that works for you.
    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. Users who have thanked Old Pedant for this post:

    doubledee (05-14-2013)

  9. #23
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Hopefully this *clarified* Requirement doesn't trash everything we've been working on?!
    Ummm...it probably does.

    But don't despair. It just means that your business model isn't correct. And you need to adjust it. It doesn't impact the derived vs. natural keys decision. It doesn't impact your idea of having a hidden "dimension" that sits outside the perceived navigation.

    Just keep working on it until you are truly happy with the model.
    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. #24
    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
    No. I am strongly in the camp of choosing the appropriate keys for the task at hand.


    Performance. CLEARLY if you have millions of records then an INT key is going to gain a *LOT* over a VARCHAR key. Simple as that.

    Even then, it depends on how hard you expect to hit the DB. That is, how many queries per minute or per second.

    But for only a few thousand records? Even tens of thousands? And on a website that gets no more than, say 10 hits per minute? You'll never ever be able to see the performance difference.

    I work on one site that gets over 300,000 database-related page hits per week. Say 48,000 on a typical day. That's 2,000 per hour, or more than one per second. And at least half of those queries (probably more like 75% of them) require a SQL WHERE clause that uses a KEY based on one or more VARCHAR columns. And we have essentially 100% uptime and very minimal response time.

    PICK YOUR BATTLES CAREFULLY. If performance is not an issue (and I can't imagine it is, on your site), then choose the solution that works for you.
    Duly noted!!


    Debbie

  11. #25
    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
    Ummm...it probably does.

    But don't despair. It just means that your business model isn't correct. And you need to adjust it. It doesn't impact the derived vs. natural keys decision. It doesn't impact your idea of having a hidden "dimension" that sits outside the perceived navigation.

    Just keep working on it until you are truly happy with the model.
    I guess that means, "Go back to your drawing board and take another swing at it (and then maybe I can help)..."

    Except, my brain is pretty fried on this entire undertaking...

    I will go back and try to figure out how to fix things, but I'm praying I can still get some help, because I feel like this is pushing me beyond my capabilities, and it is also stopping my entire website development dead in its tracks...

    Sincerely,


    Debbie

  12. #26
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    It may very well be that you have to abandon your idea of DIMENSIONS.

    Maybe you really don have to stick with a simple SECTION=>SUBSECTION=>ARTICLE scheme.

    Or maybe try an alternate approach. I am *NOT* advocating the following approach; merely showing it as an alternative.

    Suppose you had these tables:
    Code:
    CREATE TABLE sections (
        section VARCHAR(100) PRIMARY KEY
    );
    CREATE TABLE subsections (
        subsection VARCHAR(100) PRIMARY KEY
    );
    CREATE TABLE articles(
       article VARCHAR(100) PRIMARY KEY,
       articletext TEXT
    );
    CREATE TABLE section_subsection(
        section VARCHAR(100),
        subsection VARCHAR(100),
        CONSTRAINT FOREIGN KEY section REFERENCES sections(section).
        CONSTRAINT FOREIGN KEY subsection REFERENCES subsections(subsection).
    );
    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).
    );
    You see it? Now your article_placement table can have (examples only)
    Code:
    article                     | section   | subsection
    WhenASoleProprietorGetsSued | legal     | sole proprietorship
    WhenASoleProprietorGetsSued | legal     | litigation
    It's not as neat and clean as your DIMENSIONS solution, but it does allow you complete control, and it isn't too hard to visualize.

    But think about it. You could well come up with something better.
    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.

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

    doubledee (05-14-2013)

  14. #27
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,048
    Thanks
    25
    Thanked 0 Times in 0 Posts
    I am *really* getting my butt kicked by this Data Model...


    Quote Originally Posted by Old Pedant View Post
    It may very well be that you have to abandon your idea of DIMENSIONS.
    Nah, Debbie never accepts defeat!!

    So, before I read your latest post I drew out another ERD which *logically* solves my problem. (The devil is in the physical implementation.)

    I also looked at your latest proposal. It is a streamlined version, and I get it, but I think it falls victim to the same problem I keep running into.


    If you look at all of our various "plans and schemes", there is a common theme...

    The Data Models can be made to work "logically", but the challenge is, "How do you BUILD and SYNCHRONIZE the Junction Tables?!"


    (If I'm wrong here, please correct me.)

    For example, how would a person ENTER data - think in terms of a UI - to populate your latest Section_SubSection and Article_Placement junction tables??

    And how would a person MAINTAIN data - think in terms of a UI - in both of those junction tables??

    It's easy to manually populate the tables up front, but what happens when I change the relationship between Sections, SubSections and Articles? (Junction Tables need to be changed and synched as well...)


    Admittedly, yours is much easier since there are only two Junction Tables which need to be kept in synch, but this is where I think I'm getting stuck. (While I don't have the bandwidth for it, I might get stuck having to take a crash course in Triggers and Stored Procedures - assuming MySQL even has those?!)

    What do you think about all of this?

    Am I way off, or do I have a valid point?

    And any suggestions on how to fix this?


    It's a bummer, because I have an *awesome* new Logical ERD that I created last night, that could give me everything I want and need, but it also falls victim to the issue described above...

    (Back to my evil scientist drawing board)

    Sincerely,


    Debbie

  15. #28
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    "How do you BUILD and SYNCHRONIZE the Junction Tables?!"
    With PHP coding.

    You add a new article to the Articles table.

    And then your PHP code automatically shows you a list of sections (maybe a set of checkboxes) and you check all the sections you want that article to be in.

    And then the PHP code shows you each section you picked and, under each, a list of available subsections. Again, you check all the subsections you want the article in.

    And you are done.
    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.

  16. #29
    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 doubledee
    How do you BUILD and SYNCHRONIZE the Junction Tables?!"

    Quote Originally Posted by Old Pedant View Post
    With PHP coding.
    I'm still chewing on this, and how to make things work with my Section-Dimension-SubSection paradigm...


    In the mean time, is using CASCADE ON DELETE evil?


    Debbie

  17. #30
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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*.
    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.


 
Page 2 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
  •