Hi,
I have a tricky problem. To explain it I need to describe the database:
company - pk : comp_id - list of companies (2000)
product - pk : product_id - list of products (222 )
category - pk : cat_id - list of 7 categories; each product can belong to 1 or more category; hence
category_product - pk : cat_id, product_id - one row for each valid category / product combination. (295 rows)
Ok so far?
Companies can edit their own details and can make their own entries for the products they produce. These entries go into the following table:
company_product - pk : company_id, cat_id, product_id - one row for each company / category / product. (Maybe I should have called this table company_category_product - but it seemed a bit long) (4500)
All of this is working.
The problem is with the company_product add form. This has two dropdowns on it - one for category and one for product. Companies choose a category and then choose a product. But it's possible for them to choose illegal category / product combinations, and it's likely that they will.
So how to solve this?
The ideal would be that when the company chooses a category, the list of available products is dynamically updated from the database, by running a query against the category_product table. I'd be very interested in hearing from anyone who thinks they can do this, even if they want paying (GT, anybody else ...?). I'm not keen on solutions involving javascript with all the data embedded in the code...
Other workarounds:
idea 1) - build in a checking routine so illegal category / product combinations can't be saved. Companies might end up getting a lot of error messages which would be annoying.
idea 2) - split the company_product add form into two forms. On the first form choose a category and click "next". Depending on category chosen go to 1 of 7 pages where sub-list of products is displayed. This would be quite acceptable I think, and sounds easier to me than the idea solution above.
idea 3) - can't think of one.
All suggestions welcomed.
thanks
Tim Ault
Oxford UK
I have a tricky problem. To explain it I need to describe the database:
company - pk : comp_id - list of companies (2000)
product - pk : product_id - list of products (222 )
category - pk : cat_id - list of 7 categories; each product can belong to 1 or more category; hence
category_product - pk : cat_id, product_id - one row for each valid category / product combination. (295 rows)
Ok so far?
Companies can edit their own details and can make their own entries for the products they produce. These entries go into the following table:
company_product - pk : company_id, cat_id, product_id - one row for each company / category / product. (Maybe I should have called this table company_category_product - but it seemed a bit long) (4500)
All of this is working.
The problem is with the company_product add form. This has two dropdowns on it - one for category and one for product. Companies choose a category and then choose a product. But it's possible for them to choose illegal category / product combinations, and it's likely that they will.
So how to solve this?
The ideal would be that when the company chooses a category, the list of available products is dynamically updated from the database, by running a query against the category_product table. I'd be very interested in hearing from anyone who thinks they can do this, even if they want paying (GT, anybody else ...?). I'm not keen on solutions involving javascript with all the data embedded in the code...
Other workarounds:
idea 1) - build in a checking routine so illegal category / product combinations can't be saved. Companies might end up getting a lot of error messages which would be annoying.
idea 2) - split the company_product add form into two forms. On the first form choose a category and click "next". Depending on category chosen go to 1 of 7 pages where sub-list of products is displayed. This would be quite acceptable I think, and sounds easier to me than the idea solution above.
idea 3) - can't think of one.
All suggestions welcomed.
thanks
Tim Ault
Oxford UK
adding records - two select fields, one updates the other?