If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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.

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...

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...

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.

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.

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.

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...

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...