Design Confusion!



I have a very confusing scenario (at least it is for me) - scenario

I have three tables as follows;

tblSECURITY (as in financial stock)
SecurityID (PK Autonumber)
SecurityName (Text)

AccountID (PK Autonumber)
AccountNo (Number - 9 digits)
GeoCode (Number - 3 digits)

ConstraintID (PK Autonumber)
Constraint (Text)
Prefix (Number - 3 digits)

Here is a brief scenario;
Accounts are held by Canadian people NOT residing in Canada, and are
in either Canadian Currency or U.S. Currency. The account table also
has a GeoCode that designates where the people holding the account
actually reside.
Based on government rules, Constraints have to be placed against
Securities for specific accounts. In other words certain accounts can
not hold these securities. I want to keep track of what accounts have
what constraints put on them.
The constraints consist of certain kinds of Accounts; US dollar accts,
Canadian dollar accts, Registered accts, Non Registered accts
These types of accounts are designated by a 3 digit prefix on the
account number.
i.e. 530-xxxxx is a Canadian dollar account
531-xxxxx is a U.S. dollar account

So as an example:
Security “ABC” can not be held by Canadian Or U.S. Dollar Accounts
therefore any account that starts with 530 OR 531 must not hold “ABC”
Currently my tblConstriants consists of 4 records
1 Cdn Accounts 530
2 U.S. Accounts 531
3 Registered 559
4 Non-registered 561

Within a form, Upon selecting a Security & the said Constraints, i
would do a search on the tblAccounts looking for 530* or 531* and add
each of the associated AccountID's to a forth table as follows.
I am planning to set up a forth table to hold the ScerurityID,
ConstraintID, and AccountID.
So for this example I would have a table with
SecurityID ConstraintID AccountID
1 1 55
1 2 55
1 1 56
1 2 56

Is this correct? Am I on the right path.
My apologies if the explanation seems convoluted.
Thank you very much for TRYING to understand my scenario!


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