Table Design

[Ralph]

I have an income and expenditure table with the following fields:
tr_num - Transaction number
Date - Date of Transaction
MemberID - Member's ID number
Categeory - Category of Income or Expenditure
Description - Transaction details
Transaction type - Income or expenditure
Amount - Actual amount

I have been asked to setup the following additional field:
Share Transaction - share purchase/redemption/transfere
Share value - Value of each share
Number of share
Total value of shares
Transfer/sale of share to other members
Annual dividend issued
Dividend paid

Do I put all these fields in one table or in seprate table and how do I
track when shares have been transfeed or sold to ther members.

 

[Vincent Johns]

There are probably several good ways (plus some troublesome ones) to
organize your information. What I offer here are suggestions.

I assume that [Description] is a text or memo field, and that you don't
plan to do any calculations on it. If this be not true, I suggest that
you split out whatever parts you want to be computable. For example, do
you want to retrieve the name of the security (e.g., "Microsoft
common")? If so, put that part into another field, and take it out of
the [Description] field.

You might consider representing [Transaction type], since there are only
two possible values, via the sign of the [Amount] field, such as + for
income and - for expenditure. (But be careful to make it obvious which
sign corresponds to each type!)

Apparently, the first 7 fields relate to some purchase or sale of an
asset, and I'd keep them in one Table. Of the additional fields, some
belong there, too, such as [Share value]. I assume that's the value
realized from each share of stock sold. (How do you account for
commissions or transfer fees?) But I see no way that [Annual dividend
issued] would apply to a stock purchase. It might be relevant to
receipt of a dividend, but then perhaps you could record such an event
in [Category] or [Description], and not devote an entire field to it, a
field which would usually be empty. Same for [Dividend paid].

You probably don't want to include all of [Share value], [Number of
share], and [Total value of shares] in the same record. Probably one of
these can be computed from other information in the record, and
therefore belongs in a Query. Having computable information in a Table
might save time, but usually just causes headaches -- what do you do if
an inconsistent value appears there? Such as [Share value] = $30.00,
[Number of share] = 100, and [Total value of shares] = $69.95? Some of
this is evidently wrong, but which one?

What do you want to store in [Transfer/sale of share to other members]?
Maybe the [MemberID] of such other member? Then you probably need
to be sure that there's a matching record for the other half of the
transaction.

Information that I think should go into another Table might be address &
phone numbers of a member, perhaps a field showing when his membership
is due for renewal, etc.

To track when assets are sold to (or bought from) another member, you
probably have to require the data-entry person to specifically identify
such other member. For stocks sold on an exchange, you probably don't
care who bought them, but I can't think of an automatic way to track
purchases or sales by another member.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.