The object of all of this is to create a table that lists a document title, it's file type, size, and the date it was last modified sorted by sections and grouped by category grouped by project for each client.

I can get bits and pieces but then I start confusing myself! For example,

you neglected to mention what it is you're trying to accomplish with your query

The object of all of this is to create a table that lists a document title, it's file type, size, and the date it was last modified sorted by sections and grouped by category grouped by project for each client.

what is the purpose of the document_id column in the categories table?

Nothing! Sorry, I copied it from the original db design doc. It has been removed. The same with the sections table. I've updated the tables in my original post. Thanks for catching that.

if the document_sections table relates documents to sections, what is the purpose of the document_id column in the sections table?

None-it's gone! (explanation above)

also, the doc_cat_id and doc_sec_id columns are useless and counter-productive

Should I just be using document_id?

It's been a while (years) since I've worked with databases. Can you tell?

r937
—
2010-02-27T19:03:12Z —
#3

you neglected to mention what it is you're trying to accomplish with your query

what is the purpose of the document_id column in the categories table?

if the document_sections table relates documents to sections, what is the purpose of the document_id column in the sections table?

also, the doc_cat_id and doc_sec_id columns are useless and counter-productive

r937
—
2010-02-27T19:26:40Z —
#4

Ocean_Breeze said:

...sorted by sections and grouped by category grouped by project for each client.

this part is confusing

what does "grouped" mean? are you looking for a multi-level sort?

if your tables have been modified, would you mind doing a SHOW CREATE TABLE for each one please

Ocean_Breeze
—
2010-02-27T20:28:34Z —
#5

r937 said:

this part is confusingwhat does "grouped" mean? are you looking for a multi-level sort?

Each client has multiple projects. Some projects have multiple categories, others aren't categorized at all. Some documents that relate to a project may pertain to only a certain section (table of contents, report, table, figure, appendix, etc.) of the final report, so I'd like to group by section when they exist.

(it is potentialy misleading to use the term "grouped by" in this context, as GROUP BY in SQL has a completely different function from sorting)

Thanks for clarifying that!

r937 said:

okay, a client can have one or more projects -- this is a one-to-many relationship

a project can have one or more documents -- another one-to-many relationship

Yes

r937 said:

but a document can belong to multiple categories and a category can have multiple documents -- a many-to-many relationship

and a document can belong to multiple sections and a section can have multiple documents -- also a many-to-many relationship

No, actually. The way these documents are written each document belongs to only one category. A category can have several documents. The same with section--each document is only one section, but each section can have multiple documents.

r937 said:

unfortunately, pulling everything together in a single query is not as simple as it would appear

Well, at least I was stumped by something that's not actually simple and obvious!:D

r937 said:

your illustration shows sections within categories, but unfortunately there is no relationship in the tables to support this

my advice is to see if you can actually load the data into your tables that reflects this, and i think you'll see it's not possible

so you'll need to rethink the categories and sections part of the design

Do you have any suggestions on how best to do this? I kept them as separate tables (lookup tables?) because I thought that was the best db design. I didn't want to add a category_id and section_id to the documents table because not every document will have either a category or a section. Each are used only occasionally and I didn't want a table full of columns with no data. The only way I could think of to link the two was the document_categories and document_sections tables.

Now my problem is pulling it all together.

Does it make sense to do a join, create a temporary table, join to that and create another temporary table, etc. until I've pulled in all the fields I need?

I don't really understand them, but would a subquery be the way to go?

I've also seen examples of nested joins, but can't for the life of me wrap my head around them--yet. I'll keep plugging away. The light bulb comes on eventually!

[quote="r937,post:6,topic:51317"]as for the unnecessary columns, here's what the many-to-many tables should look like (assuming you need both of these tables) --

the auto_increment columns are unnecessary and counter-productive[/quote]

Thanks for this, but these are actually one-to-many relationships.

I really appreciate your thoughtful responses. It's been so long since I've done this. I start thinking I'm on the right track and then I just confuse myself. It's nice having someone explain it all and give me some input.

Cheers!

r937
—
2010-02-27T22:21:32Z —
#8

Ocean_Breeze said:

The way these documents are written each document belongs to only one category. A category can have several documents. The same with section--each document is only one section, but each section can have multiple documents.

in that case i would urge you to ditch the many-to-many tables, and put the category_id and section_id columns into the documents table, and make them nullable (i.e. NULL, not NOT NULL)

then you are left only with the issue of whether a section belongs to only one category or whether a section can be in multiple categories