Simple Business Intelligence tool

[Stevenn]

I am trying to build a simple business intelligence tool. I have a lot of mysql tables with data I want to retrieve in a user friendly way.

 

I want the user to select elements and when the elements is selected, the system will join the right tables. How is this possible? I need a hint to be guided in the right direction. I can simply not come up with an idea to a database structure in which I can collect the right table relations. Does anyone have any ideas?

 

Thank you.

[trq]

  Quote

I want the user to select elements and when the elements is selected, the system will join the right tables. How is this possible?

 

It is possible through application logic.

 

You need to dynamically add JOIN's and WHERE clauses to your database queries as different search criteria is chosen.

[Stevenn]

But I have to tell the application which tables are related and how they are related, but how could a possibly database structure be like?

[Jessica]

If they are related in a logical way, just write the logic down. For example my foreign keys are always tablename_id. If I see that I know it's related to tablename

[Barand]

maintain a "foreignKey" table eg

 

tablename | columnname | referencescolumn | referencestable
----------|------------|------------------|-----------------
invitem  | invno	  |  invno		   | invoice
invitem  | prod_id    |  product_id	  | product

 

You would also need to know that if the user wants info from both the invoice table and the product table then the query would also require the associating invitem table. The above table could give you that also.

 

Once you have determined the tables and joins required you can then display check-box lists of the columns they want included or used to search, plus what the search criteria should be for those columns

[Tychonaut]

Joins can take a bit to understand, but eventually you will be dynamically generating a query like this

(this would allow you to get customer info, his order info, and the description of the item he ordered .. contained in 3 separate tables)

 

SELECT tableA.name, tableA.address, tableA.email, tableB.orderID, tableB.orderTotal, tableC.itemDescription

FROM tableA

INNER JOIN tableB // add access to tableB

ON tableA.customerID = tableB.customerID // where they share this value in common

INNER JOIN tableC

ON tableB.productID = tableC.productID

WHERE tableA.customerID = '47655';

 

This allows you to grab information from multiple tables in the same query. You just have to make sure that there is a "shared column" or "foreign key" in common between the two tables. You are saying.. "give me the fields from tableA .. *plus* give me access to the fields in tableB where "field X" is the same as in the first table.

 

There are a few different kinds of JOIN statements, but that is probably the one you will use the most.