join problem or design problem? relate one field to many tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Using one table per category is usually a very spreadsheetly thing to do...
?Perhaps you are describing subtyping, which may be a more legitimate reason
to use this construction.

If you provide a couple examples, the 'group readers may be able to offer
alternate approaches.

More info, please...

Jeff Boyce
Microsoft Office/Access MVP
 
What are we doing wrong?
Click to expand...

Two things: one technical, the other philosophical. Let's take the second
one first because it's easier:
we lose the subdatasheet icon in the table view,
Click to expand...

Well, don't use the table view! Seriously, using table-type datasheets is
not recommended except for design and debug work;-- they should never be
exposed to your users. Use a form for editing data (in datasheet or,
better, continuous forms mode if you like the grid-type layout) because
it gives you much better control over events, validation, subforms etc
etc. Use a report if you just need to look stuff up without interacting.

Now the technical error:
we've created 20 tables based on each category,
Click to expand...

This is practically always a bad idea: it means you are keeping important
data in table names and it's not easily extensible. There is one way of
making this work called Subtyping, but it's fiddly and still has
problems. Another way, which is more flexible and probably meets your
needs is to have a single table with all the attributes in and their
values;

ObjectNumber Attribute Value
============ ========= -----
1023 colour Green
1023 size Large

1044 NumOfSeats 4
1044 EngineSize 2400
1044 GearShift Manual

and so on. You can control this by having another table like this

ObjectType Attribute
========== =========
Sofa Colour
Sofa Size
Car NumOfSeats
Car EngineSize
Car GearShift

and so on. Some simple joins will re-create the original spreadsheet, or
show up missing attributes and so on; if necessary you can put in some RI
rules to prevent recording attributes that are inappropriate for the
object type.



Hope that helps


Tim F
 
Back
Top