using multiple subtables efficiently

I'm pretty much a novice at database design. I'm sure this is a standard question, but I've drawn a blank looking for examples. If there's a more appropriate group for this query please point me at it!

I have table A, and subtables a and b. The relationship of A to any subtable is 1 to 1. A record in A will have a related record in only one subtable, or none. (Subtables may have further subtables to which the same rules apply).Table A will have a few thousand (active) records.

Table A holds general product information and the subtables hold product-specific information. To generate a product list I do not need any of the values in the subtables, but I do need to know whether a record in A has a related subtable record to determine product type.

I handle this by using multiple OUTER JOINs between A and the subtables a and b, and select a.id and b.id. (the product type corresponds to the subtable where id is not NULL). When I need to display the full product information for a specific product I use a multiple OUTER JOIN.....WHERE A.id = [specific product id] to retrieve all possible subtable records and determine the product type and display values in the same way.

I now need to increase the number of subtables. If I keep the same structure I will be OUTER JOINing table A to 8 or more subtables in the same query. I know the actual query would work, but is it a sensible and efficient way to handle the subtables?

Alternatively, I could have a field in table A specifiying which subtable to use (probably a FK to a "subtables" table). This would remove the need for a join in the product list query completely and mean that the product details query only needs an INNER JOIN to one table. However, it does involve duplication of information because the product type is represented both by the A.productType field and by the existence of a related record in the appropriate subtable. Is that a better way to go?

Thanks for any help

Liz Received on Tue Sep 25 2001 - 15:14:14 CEST