[SOLVED] VIEW vs JOINs vs extra column in one table

[davestewart]

I'm quite the database intermediate, which could be a dangerous thing (!) so I thought I'd ask some questions here first.

 

I've got a range of tables in my application, and for most of my queries I need to do one or more JOINs. I have one particular query which will JOIN across 5 tables, and due to the way I'm about to allow all users to now belong to more than one group, I have to add a 6th (relationship) table to this (and most other) queries.

 

The thing is, quite a few of the multiple JOIN series could be omitted if I just added a kind of "shortcut" column (that effectively give me the same information as the JOINs) to one of the right-hand tables, and I'd be able to do quite simple queries. Once new entries have been inserted into the table the data in this new entry will never change, so, my question is, now that all the queries are becoming quite complex, how to optimize both the code and the performance?

 

• Do I stick with JOINs and retain full normalisation?
  
• Do I start using a VIEW (I'm new to views)?
  
• Or do I add this new column and just start querying one or possibly two tables max rather than a hefty series of JOINs each time?
  

 

I realise that there will be a minute level of redundancy with the last method, but it it worth it at the expense of cheaper (I presume) DB calls?

 

At the moment the tables have a few hundred rows, but ultimately I expect them to have a few hundred thousand.

 

Your thoughts would be really valuable, thanks.

 

Cheers,

Dave

 

[dzelenika]

What are your queries serving for?

 

Showing reports or individual records?

[davestewart]

Well, I have a hierarchy of tables something like this:

 

users

users_companies

companies 
services 
invoices 
items 

 

So say I query something like this:

 

get all items for all unpaid invoices for company x (in the past 6 months) for user y

 

That makes quite a few joins as everything is perfectly normalised.

 

But I could include a reference to the company in each invoice, or even item table, which would save me deriving the company by JOINing up the chain to :

 

services
companies 
users_companies

 

(users_companies is a relationship table as users can potentially belong to many companies).

 

Does that make sense?

[dzelenika]

I'm not sire I have understand your db structure, but I think that You should create and use STORED FUNCTION which calculates company or forwarded invoice.

 

[davestewart]

I could use a stored function, but that won't actually affect the efficiency of the call will it? It will just package the call into something accessible.

 

I was more interested in the actual performance benefits of one method over the other

[fenway]

I wouldn't de-normalize until you start to see serious performance issues -- and there are all sorts of ways around that too without messing with the underlying tables (caching and such).

[dzelenika]

I wouldn't de-normalize until you start to see serious performance issues -- and there are all sorts of ways around that too without messing with the underlying tables (caching and such).

I agree with you. De normalizing can cause problems when updating.

I'm using views only for reporting.

For showing individual or few rows I prefer select from master table then select for each child table instead of using JOINS. That is not cumbersome when using DAO.

[davestewart]

OK, thanks very much everyone for your thoughts.

Cheers,

Dave