Database Design

[dhstein]

This is a database design question. I need to provide an analogy of what I'm
trying to do. Imagine there are 10 types of products - call them A - J.
Products A - F have a characteristic that is further defined in another table
- call that table T and that field Q. Products G - J don't have that. I
want to link that field in the product table to the corresponding field in
table T so I can query all products where Q = 22. But since some of the
products don't use the field - this won't work. I could also create separate
tables - one table for products A - F and one for the others. But there are
some queries that will need to run against the combined tables. So I suppose
those 2 tables need some common field (like product code) to relate them. As
I'm writing this I think I'm getting close to what I need. But if you can
add any clarity or advice here that would be appreciated. That assumes that
I was clear enough - if not - my apologies.

 

[KARL DEWEY]

Create a one-to-many relationship between product table and table_T.

Use form/subform for product/table_T with Master/Child links set on product
primary key field and table_T foreign key field.

Whenever you pull data from both table use a LEFT join - product to table_T.

 

[John W. Vinson]

This is a database design question. I need to provide an analogy of what I'm
trying to do. Imagine there are 10 types of products - call them A - J.
Products A - F have a characteristic that is further defined in another table
- call that table T and that field Q. Products G - J don't have that. I
want to link that field in the product table to the corresponding field in
table T so I can query all products where Q = 22. But since some of the
products don't use the field - this won't work. I could also create separate
tables - one table for products A - F and one for the others. But there are
some queries that will need to run against the combined tables. So I suppose
those 2 tables need some common field (like product code) to relate them. As
I'm writing this I think I'm getting close to what I need. But if you can
add any clarity or advice here that would be appreciated. That assumes that
I was clear enough - if not - my apologies.

You're very clear, and this is a known (if somewhat obscure!) database
construct called "Subclassing". It's one of the rather few cases where
one-to-one relationships are used.

You would have a master Products table with a primary key ProductID, and those
fields which are common to all products. There would be a second table (your
table T) which contains only those fields pertaining to products A through F.
It would be related one-to-one to Products, and would also have ProductID as
its primary key.

You can create a query joining Products to T by ProductID, using a Left Outer
Join; the field Q will be Null if there is no corresponding record (i.e. for
table H or I). You just need to design your queries to accommodate the fact
that the field may not exist for some records.

So you're exactly on the right track!