How to query the names of subclasses

[user2k7]

Hey there!

I currently working with mySQL and JSP to build an eCommerce site for a fictional company selling components for desktop computers. The project is part of my course at the university.

In the database we have made a superclass products, with the following subclasses: harddrive, monitor, graphic cards etc.

In the user interface, when adding new products, I want the user to be able to select what kind of product to add (i.e. what subclass to use). The question is, how can I query the name of the subclasses, so I don't have to put the name of any potential new subclass into the jsp code?

[aschk]

You need to tell us more about the relations between your tables, what columns they have (including types) and then we may be able to help you out.

Going by what you have said already I would guess that you're modelling the products as a table and the product_types as a table.

e.g.

CREATE TABLE Products(
ID INT UNSIGNED NOT NULL,
Product_Name VARCHAR(100),
Description VARCHAR(255),
Product_Type INT UNSIGNED NOT NULL
)

Product_Types(
ID INT UNSIGNED NOT NULL,
Type VARCHAR(100)
)

Also as a recommendation I would use InnoDB table types to enforce foreign key constraints on the product_type. Thus :
ALTER TABLE Products ADD FOREIGN KEY (Product_Type) REFERENCES Product_Types(ID) ON UPDATE CASCADE ON DELETE CASCADE


... Aside form my advice. I need to know roughly what it is your achieveing. Are they choosing a sub type (i.e. hard drive type) and then choosing the relevant products from a list or what is it you're expecting the user to be able to do.