[thelist] Database Structure Assistance and Populating Options

[Michele Foster]

Hi folks,

I have a couple of questions.  I'd like to see what more experienced
developers would do to handle the situation.  Both questions have to do with
one particular table within my database.  Let's call it categories, with
only three columns, autogen (PK), main_cat, second_cat.  Autogen is
obviously the unique identifier, as main_cat will be a list of approximately
20 different categories, and the second_cat will be different category
descriptions within a particular main_cat.

Possible data:

main_cat - Internet, second_cat "web hosting firms", "ISP", "web designers",
"web programmers".

So, in the above case, "Internet" would appear four times in table to help
identify the four options in second_cat. As such:

1,Internet,web hosting firms
2,Internet, ISP
3,Internet,web designers
4,Internet,web programmers

First question.  Using DISTINCT in an SQL statement I can easily pull out
the entries in the main_cat column.  From that, a user selects to see the
second_cat, using a simple Where main_cat = ??

I have two options I can think of.  I'm really interested in getting
comments about performance and ease of use.  The first option, is to pass
the information from the first select box, to the existing page, rewrite
(process) the page, with another drop down box of the second_cat items.
Simple enough?  Or am I better to use a JavaScript method to populate the
second list?  Alternatively, I could lead users to a second page that will
display the options, though, I'd rather keep the "search" page identical.
Thoughts on this?

Now for my second question, with the above scenario, I would think it would
be best to use the autogen field to be the identifier once the user has
selected both sets of categories that they are searching.  In other words,
for item 3, pass just the "3" as the hidden value to the processing page
that will display all the companies listed under Internet, Web Designers.
Therefore, all that will be stored will be the autogen number which has been
selected by the individual company as a service they offer.  (Getting rid of
the text which takes up more room, thereby making the database smaller and
performance improved.)

Now the REAL question, each company can offer more than one service.  How
should I store this information?  Easily I can store it as a text field such
as "3, 4" by using check boxes of the same name and inserting the string
into the company table.  BUT, this is where I'm not sure this is the best
approach.  The autogen is looking for a number.  So, does this mean I need
to have separate fields to insert each individual category set?  I'm
guessing yes.  But, how is this for performance?  When searching to select
out the companies that have a 3, I would need to sort through multiple
columns to get it.  Possible? I think it is doable with various "where" and
"or" statements in the SQL.  I'd just like to know if performance wise this
is a good idea or if anyone has a better approach they could suggest.

Many thanks in advance, you folks are wonderful.

Michele

___________________________

Michele Foster
WordPro Services
Aurora, Ontario, Canada
mailto:michele at wordpro.on.ca
http://wordpro.on.ca/