Getting the most efficient query based on multiple tables/primary & foreign keys

I have a site where some pages (we call them gateway pages) are based loosely on certain departments in the organization. Each department has classes associated with it. Unfortunately some of my pages are not associated with a specific department, but do display information about several classes from a department so I can't just query the database strictly on department alone.

Would it be smarter to create a table called gateway_classes with a fk from the gateway table in each class or form a query to somehow filter out exactly what I need from my existing tables using an array of classes to be pulled during the query?

Here's my tables: departments_classes | classes_vendors | departm开发者_Go百科ents | vendors | classes | products | gateway

Any guidance is greatly appreciated.

More Info: There are roughly 350 classes and 18 departments and 12 gateway pages...

Your indexing table idea sounds like it'd work just fine. The only downside to that is that you've got to maintain it separately, and you want to make sure that the data you hold in that table isn't being duplicated in any of your existing tables.

If you don't want to maintain that data differently than you're currently doing so, you can use CF's arrays (or structs) to hold that correlation data (which you'd have to pull from the db in a separate query) and then loop over it as you construct the query that pulls the classes for a given page.

Either way would work okay, it's more a matter of how you prefer to do it, and what you think would be easiest to build, test, and maintain.

One thing about efficiency - make sure you not only link your tables via Foreign Keys (which helps to maintain data integrity), but also put in (nonclustered) indices, which helps the efficiency of the joins and lookups your queries will be doing.

I've seen dramatic speed improvements in my queries (CFQUERYs operating against MS SQL) with the simple act of putting in indices.

In MS SQL, you do so like this:

CREATE NONCLUSTERED INDEX yourIndexName ON yourTableName(yourFieldName)

I hope this helps!

Your problem sounds similar to a common scenario for determining user rights. A User may belong to some Group that has Rights associated with it or the User may be assigned Rights individually. In your case, the User is the Gateway, the Group is the Department, and the Rights are the Classes. A Gateway can then be linked to any number of Departments and/or Classes.

Using this model, you just need to add the gateway_classes table as you describe along with a gateway_departments table.

You could then use UNION to merge the "gateway classes" query with the "gateway departments" query (or perhaps something more elegant) but I think this schema will do want you need without introducing any redundant information.