expandable navigation 1

[darryncooke]

First off I am looking more as to where to start this journey.

I am building a local business directory of which I have over 100K records.

Each record has a colum 'primary industry', 'secondary industry', 'primary line of business', 'secondary line of business'.

So lets say the 'primary industry' are the macro fields (i.e. leisure, manufacturing, retail, etc.) 'Secondary' would be if the company fell into 2 categories.

'Primary line of business' would be like Restaurant, Apparel, and 'Secondary line of business' would be Italian, Mexican, Men's, etc.

can I use PHP to build this navigation (just like any other directory) where it pulls results from the colum where there are at LEAST 25 records.

IE if there are 26 Italian Restaurants then that category would show. Other wise it wouldnt.

So it would look like this:

Leisure
Restaurants
Italian (35)
Mexican (100)
Arcades

I want this to only be for the Line of Businesses. Also how would I show the count, and is this the most effecient way to do this?

I was thinking about making a separate table which would be just the navigation but with the number of records I just dont have the time to clean up the data completely. I have done about 1/4 of it and it has taken me about a week straight and I dont see an end in sight.

Thank you,

Darryn Cooke

http://www.darryncooke.com

| The New Orange County Graphic designer and Marketing and Advertising Consultant

http://www.bloobie.com

| Marketing and Advertising blog

 

[jpadie]

the answer is of course a la Bob the Builder: Yes You Can!

but may i suggest a tweak? store the 'lines of business' differently. instead:

2. create a table for 'linesofBusiness'
3. create a join table to handle business->lines of Business.

like this

create table blobjoin
( businessID int(10) not null,
lobID int(10) not null,
primaryBusinessType tinyint(1) default 0,
primary key (businessID,lobID));

this would then allow for more than one second line of business.
and it is a simple matter of a count query to determine whether or not there is are more than 25 instances of the sub-business type.

one other potential bit of advice: consider wordpress as a management tool for your site what you are looking to do has considerable cross over with hierarchical categories, which are native to wordpress. Equally the same is most probably true with other blogging engines and quasi-CMS apps.

 

[darryncooke]

I did think about WP however the platform that I want to create will have a pretty decent permission based system which will be how I plan on generating revenue.

I am not sure that WP will allow me the ability to create such pages and conditional areas on page without having more control over the code.

The thing however then would be that I would have to have the table with the content (not the Line of Businesses) have an id that matches the corresponding LOB. If thats the case then I would do this for the Primary Industry as well. This seems more efficient as I dont think querying a 100K+ rows of data is a very efficient way to build this navigation. Im guessing thats going to put a strain on the server?

Darryn Cooke

http://www.darryncooke.com

| The New Orange County Graphic designer and Marketing and Advertising Consultant

http://www.bloobie.com

| Marketing and Advertising blog

 

[jpadie]

wp: permission based system: role_scoper plugin. it's about as granular as you can get.

querying a table with 100k lines is not a significant overhead. that's a relatively small database. and yes, you would need a join table as per my first post. all joins would go there, not just primary or secondary. you would distinguish between primary and secondary by setting the third field to 1 for primary.

since the combo of field 0 and 1 are the table's primary key i think you will see optimised searching and very little performance lag (if any is noticeable at all). from my perspective this represents the most efficient schema, not to mention one that is normalised, although you will get a more considered response from the mysql forum.