single field to multiple tables-join problem or design problem?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gillfish - 26 May 2006 15:17 GMT

Hi,
not sure if this is a join type problem or a design issue.
we have a master table listing of objects, object # as a primary key.
Objects of different categories have different descriptive qualities, so
we've created 20 tables based on each category, which have descriptive fields
unique to each, with an object#field to use as primary key again.
We thought that an inner join from the mastertable.object # to each
object#field in the related tables would allow us to pull up the correct
related descriptive info for each record in the master.
BUT, one we relate the field to more than one table, we lose the
subdatasheet icon in the table view, and running a query on the master and
two related category tables gives us blank results.

What are we doing wrong? Should we be using a different relationship? Is
there another way to get the specific descriptive fields in the results by
design?

BTW, i've found these discussion groups IMMENSELY helpful on several
occasions. I think they're an awsome resource, and am very thankful to those
who contribute expertise.

Signature

Reply to this Message

KARL DEWEY - 26 May 2006 16:12 GMT

>>we've created 20 tables based on each category, which have descriptive fields

unique to each, with an object#field to use as primary key
Why not ONE table with the following fields --
CategoryID
Object#
Description

> Hi,
> not sure if this is a join type problem or a design issue.
[quoted text clipped - 16 lines]
> occasions. I think they're an awsome resource, and am very thankful to those
> who contribute expertise.

Reply to this Message

Gillfish - 26 May 2006 17:28 GMT

For each of the 20 categories (category is material type), there are anywhere
from 3-5 descriptor fields that are unique to that type of material. For
example, the master table lists objects that are material categories = slate;
mica;soapstone; wood; baleen; wood and so on.
Each material category has different descriptors used only for that
material: slate descriptors (cut, carved, notched, flaked), baleen
descriptors (knotted, sample, woven), soapstone descriptors( decorated,
perforated, stitching holes).
The user wants to see just the details that pertain to that object according
to it's material type.

Signature

> >>we've created 20 tables based on each category, which have descriptive
> fields
[quoted text clipped - 24 lines]
> > occasions. I think they're an awsome resource, and am very thankful to those
> > who contribute expertise.

Reply to this Message

KARL DEWEY - 26 May 2006 21:21 GMT

How about this approach --
TABLES
Object –
ObjectID - autonumber – primary key
Description – text
Weight – number single (grams)
Height - number single (CM)
Width - number single (CM)
Length - number single (CM)
Circumference - number single (CM)

Material –
MaterialID – autonumber – primary key
Name – text

Descriptors –
DescriptID - autonumber – primary key
Characteristics – text

RELATIONSHIPS --
ObjectMaterial -
ObjectID – number integer – foreign key
MaterialID – number integer – foreign key
DescriptID – number integer – foreign key

MaterialDescript –
MaterialID – number integer – foreign key
DescriptID – number integer – foreign key

Object.ObjectID one-to-many ObjectMaterial.MaterialID
Material.MaterialID one-to-many ObjectMaterial.ObjectID
Descriptors.DescriptID one-to-many ObjectMaterial.ObjectID

> For each of the 20 categories (category is material type), there are anywhere
> from 3-5 descriptor fields that are unique to that type of material. For
[quoted text clipped - 35 lines]
> > > occasions. I think they're an awsome resource, and am very thankful to those
> > > who contribute expertise.

Reply to this Message