Jump to content

Simple Business Intelligence tool


Stevenn

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.