[thelist] Database Structure Assistance and Populating Options

[rudy limeback]

>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 = ??

hi michele

yes, where main_cat='Internet' works, but if the user is making a choice
that's being sent back in a select field, i'd use the PK values instead

for your structure i would've had two tables, but your single table is fine
unless it gets *really* big, only then would you have to worry about the
space that all those redundant main_cats are taking up...

>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?

if you want to be sure and cover everybody, including those who don't have
javascript, then rewriting the page works nicely

don't show the second dropdown the first time, wait till they've chosen the
main_cat, then show the second dropdown when the page is shown again

on the other hand, as long as there aren't prohibitively many main and
second categories, you might also send everything and let javascript
configure the second dropdown from a choice on the first (this requires a
bit more coding, see Universal Related Popup Menus
http://webreference.com/dev/menus/ for this script)

>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.

this is always the best way to go, the reason you have PKs (primary keys)
is so that you can rely on them in other queries

>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.

no, don't do this

stringing and unstringing keys from a text field is no way to handle a
many-to-many relationship

if each company can belong to more than one second_cat, you need a separate
table for the "intersection" between companies and categories

"intersection" or "junction" or "cross-reference" table is just what i call
it, those are not necessarily recognized database terms, not unless you go
back to codasyl network databases  ;o)

anyhow, the intersection table allows you to break a many-to-many
relationship into two one-to-many relationships, where the intersection
table is child to two parent tables

keep your category table as is or split it into a heirarchy, but second_cat
will be in a many-to-many relationship with companies, each of these is a
parent table in a one-to-many relationship with the intersection table
which will be called something like company_cat and look like this

  create table company_cat
     ( compcat_id   autonumber  primary key
     , compcat_company  integer  references company(company_id)
     , compcat_category integer  references categories(autogen) )

notice i had to use "autogen" from the categories table (if that's the
actual column name), this identifies the specific row, with the specific
second_cat, that the FK points to

then just fill up this table with as many rows as necessary to represent
the company-category relationships


>  But, how is this for performance?

fabulous, and since it's the only sane way to structure the data, just go
with it

your queries will typically require joining at least two if not all three
of these tables, usually with a specific value for a field or fields in one
of the tables

for example, find all the web designer companies

  select company_name
     from categories, company_cat, companies
where main_cat="Internet" and second_cat="web designers"
   and second_cat = compcat_category
              and compcat_company = company_id


hope this helps, if not, just keep those database questions coming, i love
this stuff...



rudy
r937.com