Working with optional "to-one" tables

  • Thread starter Thread starter Maury Markowitz
  • Start date Start date
M

Maury Markowitz

I have a table that includes details for a large number of stocks and
bonds. Many of the columns in the table store data for only one of or
the other of these classes (ie, dividends vs coupons) and is optional
in any case. I would very much like to remove these columns from the
main table and move to a more normalized data representation.

Doing so, however, can greatly increase the complexity of dealing with
that data in Access. Or maybe I'm just not doing it right. Ideally I
would like to use a view that joins the data back together into an
analog of its original presentation, and use that view in the few
forms where this data appears.

However, I'm not sure if this works - what happens if there is no row
in the optional table? Will Access handle these cases? I would really
like to avoid creating and deleting these rows if possible.

Subforms seem like a potential solution as well. However, I've always
found them difficult to work with. Is this the way to solve this
anyway?

Maury
 
So you plan to use a table like this:
- StockID relates to your table of stocks/bonds.
- TypeID 'dividend', 'coupon', or other type
- TheValue Currency

You could then display the data as in your original table via a crosstab
query where:
- StockID is the Row Heading (Group By)
- TypeID is the Column Heading (Group By)
- TheValue is the Value. (First)
(Crosstab results are read-only, of course.)
 
I like one-one relationships a lot.

I think that they're generally under-used.

I sometimes use a trigger in order to implement business logic,
sometimes, it's apporpriate to automatically create a new record in
the smaller table, triggers can do this reliably and easily.

I don't think that Jet offers triggers support, so I try to stay away
from Jet (I heard it's depecrated)
But SQL Server supports triggers ;)

HTH

-Aaron
 
Maury,

What you are suggesting is a standard approach where you have a general
category (investments) that has multiple subcategories (stocks and bonds).
The Investment table would have the attributes that are common to all the
subcategories, and then each subcategory would have its own unique
attributes. An inner join from investments to stocks gets all the stock
data, and similarly for the bonds.

The advantages of doing it this way:
a) Clearer match between reality and the db, meaning the model is more
representative of the business.
b) Allows subcategory-specific relationships, validation rules, etc. I think
this is the most valuable, if it applies. This lets you set relationships
that apply to all subcategories (by relating to the parent Investment table)
or to any one of the subcategories.
c) Potentially saves storage space since each Investment only stores the
attributes that apply to it. This is somewhat balanced though by having the
additional tables, foreign key attributes and indexes. Plus you need an
InvestmentType attribute in the Investment table, but maybe you would have
already had that one.

The major disadvantage is that the developer has more work to do. If you use
separate data entry forms for each subcategory, the extra development work
is pretty minimal. You use the appropriate joins for each subcategory as the
form record source, and Access takes care of the rest. If you want to have a
general Investment form, which is usually simpler for users, then you need
to add code to show and populate the correct subform for each subcategory.
If you allow changing the investment type, you have to be careful to catch
all the UI events that can affect an investment. That's easier now that you
can add code to the Undo event. But you have to be careful and thorough.

I don't find that reporting is much more difficult. If you want to recreate
your original all-in-one investment table, you can use a union query of the
individual queries that combine the investment table with each subcategory.
Something like:

Select ...
From Investment Inner Join Stock On ...

Union All

Select ...
From Investment Inner Join Bong On ...

You just have to make sure your Selects include nulls for the fields that
don't exist in the particular subcategory, since the union requires
identical field sets in the subqueries.
 
is pretty minimal. You use the appropriate joins for each subcategory as the
form record source, and Access takes care of the rest.

I guess this is my question, right here. If I use the JOIN approach on
normalized tables, will Access do the "right thing" in terms of...

1) inserting a new row in the fkey'ed tables when needed
2) deleting a row when appropriate

Normalizing only saves space if the formerly sparse columns turn into
normally sparse fields. However, I've worked with many products that
demand the associated row exist in advance, or improperly clean up all-
null rows. Is Access an exception?

Maury
 
My memory is that Access will correctly insert, update and delete when you
use the Join approach, taking care of both the generalized parent table the
subcategory child table. If I remember correctly, you might need to alias
the linking fields so you can include both the parent's key and the child's
key in the recordsource. It might not insert the child row unless there is
at least one data field, but you could set the child's FK value in code
during an insert if necessary. You should set the table relationships to
cascade updates and deletes.

What it definitely won't do with the Join approach is support changing the
type of a generalized parent row. For that you need a more complicated form
with your own coding.

is pretty minimal. You use the appropriate joins for each subcategory as
the
form record source, and Access takes care of the rest.

I guess this is my question, right here. If I use the JOIN approach on
normalized tables, will Access do the "right thing" in terms of...

1) inserting a new row in the fkey'ed tables when needed
2) deleting a row when appropriate

Normalizing only saves space if the formerly sparse columns turn into
normally sparse fields. However, I've worked with many products that
demand the associated row exist in advance, or improperly clean up all-
null rows. Is Access an exception?

Maury
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top