relationship question

[bazz]

HI,

I have three tables like this:


tbl_BusinessType
Business_Type_ID | Business_Type

tbl_Business_Sub_Type
Business_Sub_Type_ID | Business_Sub_Type

tbl_Category
Category_ID | Category



I want to relate these for the purposes of making a triple combo drop down so, is this the way to make the relationship or, is there a better way.

a table like this:


m2m_Types_SubTypes_Category
Relationship_ID | Type_ID | Sub-Type_ID | Category_ID



if so, what should the query be like?

bazz

[TheShaner]

We need a little more info on how these tables actually relate. I'm not a big fan of creating tables that store relationships. It creates a lot of overhead and very complex queries.

It seems as if you would have a table that stores each business. Each business will have a business type, sub-type, and category, like so:
tbl_Business
Business_ID | Business_Type_ID | Business_Sub_Type_ID | Category_ID | Business_Descrip
Am I right on that assumption? If so, then there seems to be no relation on the three tables. They're just tables that store values to pick from that will go into the business table and the only relation they will have is to the actual business table as you can use the IDs in the business table to look up the actual descriptions.

How are you imagining this combo box working as it will give us a better idea on how those three tables are to relate if I'm wrong on my above assumption?

-Shane

[Fumigator]

Guelphdad has a tutorial on how to do this here (http://www.guelphdad.wefixtech.co.uk/sqlhelp/catsubcat.shtml).

[bazz]

Thanks both.

@TheShaner:
You are correct with your notion about the business table. However, separate from that, I need to be able to build a triple combo (in JS), which will show the business type in the first DD. Selecting one of the choice, the second dd will show only those Sub Types, which are related to the Type. And the selection in that second DD will make the third box show only the categories that are related both to the Type and the Sub Type.

I had planned to use the tables as shown in the first post as I thought it would be quicker (use less resources too), if I had to query three tables of a toal of about 50 entries instead of one which had 1 000 000, or more, entries.

Any further advice would be most welcome.

@Fumigator:
I had overlooked checking Guelphdad's page. :o However, I shall re-read it tomorrow as I don't actually see the query of the three tables. Still, it feels like I am about to jump right into the 'joins' process, which I have be stalling for a while. They are almost as much a mental stumbling block as was, is still, perl hashes.

bazz

[Fumigator]

Yeah joins are something best understood by seeing the results they produce.

[TheShaner]

@TheShaner:
You are correct with your notion about the business table. However, separate from that, I need to be able to build a triple combo (in JS), which will show the business type in the first DD. Selecting one of the choice, the second dd will show only those Sub Types, which are related to the Type. And the selection in that second DD will make the third box show only the categories that are related both to the Type and the Sub Type.
Ah ha! That's exactly what I needed to know. This is how I would set it up:
tbl_BusinessType
Business_Type_ID | Business_Type

tbl_Business_Sub_Type
Business_Sub_Type_ID | Business_Sub_Type | Business_Type_ID

tbl_Category
Category_ID | Category | Business_Sub_Type_ID
The second two tables contain the ID of the previous table's ID to relate back to those records. For example:
Bus_Type_ID Bus_Type
1 construction
2 ecommerce

Bus_Sub_Type_ID Bus_Sub_Type Bus_Type_ID
1 online auctions 2
2 online shopping 2
3 commercial 1

You will do the same for tbl_Category relating back to tbl_Business_Sub_Type.

A query may look like this:
SELECT Business_Sub_Type
FROM tbl_Business_Sub_Type
WHERE Business_Type_ID = <hidden field modified by JS to contain the ID after first DD selection>
-Shane

[bazz]

@TheShaner,

Thanks for that. I was thinking more along the lines of bringing in all the data and using the JS to change it as required. I seem to glean from your post that your suggestion means a secondary request from the DB, based on the choice of the user in DD#1 must be made? Maybe I misunderstood?

bazz

[Fumigator]

Either way you do it, you'll want a well-designed data structure. I too would lean toward the combined PHP (or whatever) plus Javascript solution.

[TheShaner]

@TheShaner,

Thanks for that. I was thinking more along the lines of bringing in all the data and using the JS to change it as required. I seem to glean from your post that your suggestion means a secondary request from the DB, based on the choice of the user in DD#1 must be made? Maybe I misunderstood?

bazz
Yes, my suggestion would be using an AJAX solution to query the DB after the first drop down is selected and then again when the 2nd drop down is selected. However, you could also pull all the info from the DB if you really wanted and then use javascript to filter through the results as each drop down is selected. For the latter solution (the one you're thinking of), you'll have to do three separate queries and store each query's results into three separate JS arrays and then based on their selection in the drop downs, pull the correct info from the arrays.

-Shane

[bazz]

Thank you very much. I think I should be better to do the three queries so that all the necessary data is in the script, instead of AJAX queries being done. This I think would be q quicker, more user-friendly (instant) user interface though if I am wrong in this thought, do tell me.

Something I recall from Guelphdad... let MySQL do the heavy lifting and then process it.

bazz

[TheShaner]

Thank you very much. I think I should be better to do the three queries so that all the necessary data is in the script, instead of AJAX queries being done. This I think would be q quicker, more user-friendly (instant) user interface though if I am wrong in this thought, do tell me.

Something I recall from Guelphdad... let MySQL do the heavy lifting and then process it.

bazz
No matter which way you look at it, you will be doing three queries regardless. However, with your approach, your 2nd two queries will be yielding larger results since they're returning all records from the Sub Type and Category fields rather than selecting only those records based on the previous drop down. You will also be doing more JS work since it'll have to have three full arrays of info.

Also, I think you're a bit confused on what AJAX is. It's just javascript calling a server-side script (PHP script) that queries the database and then sends its results back to your javascript to display in real time. So MySQL is still doing the "heavy lifting". It's also just as user friendly as your approach. In addition, it's less overhead since the queries will be yielding fewer results.

Honestly, I would look into the AJAX approach. I don't really like the thought of three tables being queried for all their data to be stored in JS arrays, which makes all your data much more visible (in source).

-Shane

[Fumigator]

Theshaner if we're talking about just a few dozen (or even a couple hundred) rows then overhead is inconsequential. If there are thousands of rows being queried then maybe I could buy into the overhead issue but I doubt we're dealing with that much data.

[TheShaner]

Theshaner if we're talking about just a few dozen (or even a couple hundred) rows then overhead is inconsequential. If there are thousands of rows being queried then maybe I could buy into the overhead issue but I doubt we're dealing with that much data.
I'm not just referring to the "inconsequential" overhead from MySQL but also the extremely bloated code since all three tables will be dumped into JS arrays (eww) and the additional search and filter time for the JS script to sift through the records rather than allowing MySQL to filter the records (which would support guelphdad's message of allowing MySQL to do the heavy lifting).

On another note, debating this is detracting from the main message that his method of dumping all three tables into JS is far more inefficient than using a clean, AJAX approach.

-Shane

[bazz]

OK guys I take your views on board. I'll look into ajax.

bazz

[Fumigator]

all three tables will be dumped into JS arrays

This will only be a problem if there is a large amount of data. IMO, of course. You obviously hate the idea, and that's your opinion. It's also a matter of opinion that AJAX is a clean approach. I'm sure you've sat waiting on an AJAX response for upwards of 10 seconds before on various webpages... Javascript is all client-side and therefore extremely fast. With AJAX the client-to-server communication is tripled, along with all the overhead that comes with it. Sounds less efficient than one client-server transaction that gets all data needed and lets Javascript handle the data from there.

I hope you can see that this debate, rather than detracting, speaks to the very question we're asking here: How can the task of dynamically loading listboxes best be achieved?

[bazz]

Mmm interesting.

With my MySQL limited (very limited), experience I had thought about the several queries from the DB and the time this would take. I had thought that if all the data was collected 'in one go', then JS could process it quickly and beter for the user.

It won't (in my case), as was suggested, give away too much info about my Db so I needn't hold that as a consideration.

So I shall go with the query and JS route. perl is very good at putting this data into the various formats I have needed which in this case is JS arrays. I reckon it may be better than php though that is another issue.

thanks for your responses guys and please, look out for my Q's here for joins :(

bazz