[thelist] Database Structure Assistance and Populating Options
rudy limeback
r937 at interlog.com
Tue Sep 19 00:54:33 CDT 2000
>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
More information about the thelist
mailing list