Database design

[ckloch]

I have a database which tracks client investments. Periodically we
have to recommend a change to these investments. When we recommend
changes I need to print off a form, which shows the old investments,
the ticker, and the investment category along with all of this same
information for the new investment.

I have a table that has all of the investments offered for all
clients. This table contains all of the information regarding the
investments. Right now I have a form that assigns investments to
clients. In the investment table there is an investment nubmer that
is the primary key. This is an autonumber field.


In the client table the client number is the primary key and it
contains all of the information regarding the client.


Then I have two more tables. The first table has the client number,
if the investment is actively used, and an ID, which is the primary
key.


The second table has the investment ticker, the effective date, the
id
(primary key), a look up the the client nubmer from the table
mentioned above. And possibly the proposed new investment ticker.


I have tried numberous ways of setting this up. Inevitably I get to
a
query and when I try to pull the new investment category and name, I
get the information from the old investment.


I have tried foreign keys but that doesn't work since the proposed
new
investment is not always data in this field.


I am at wits end on designing this and am desperate for help...

 

[Guest]

Please 'splain the purpose of this table
if the investment is actively used, and an ID, which is the primary
key.

Does this table have the investmentID? Are there multiple records for each
client? Can it be linked to the following table?
id (primary key), a look up the the client nubmer from the table
mentioned above. And possibly the proposed new investment ticker.

Does the "second table" contain only active investments?

 

[ckloch]

the primary key is the id. This table does not have the investment
ID There are no multiple client records in this table. and their is a
one to many relationship between the account numbers... but it isn't
linked....

There are active and inactive investments in the second table...

does this help? I wonder... these two tables were orginally one
table and table analyzer suggesting splitting them but now I wonder if
that was a good idea....

 

[Guest]

Let's talk about the entities, their relationships, and the business need,
not the tables - so I can better understand. Here's what I hear:
**There are many clients, each with attributes
**There are many investments, each with attributes
**A client can have many investments through time; they can switch
investments, making the old one "inactive" and the new one "active".
**A client can have one (or many?) active investment(s?).
**There is an investment ticker but I don't understand this and its relation
to investment

***You need to look up potential new investments for clients.
***You need to assign new investments to clients, making them active.
***You need to make existing investments for a client "inactive" after
switching them to a new investment.
***You need to keep a history of a client's investments

Can you verify or correct these statements?

 

[ckloch]

**There are many clients, each with attributes CORRECT

**There are many investments, each with attributes CORRECT

**A client can have many investments through time; they can switch

investments, making the old one "inactive" and the new one "active". CORRECT

**A client can have one (or many?) active investment(s?). CLIENTS
GENERALLY HAVE MANY INVESTMENTS


**There is an investment ticker but I don't understand this and its
relation

to investment THE TICKER IS THE INVESTMENTS SOCIAL SECURITY NUMBER

***You need to look up potential new investments for clients. YES
FROM THE INVESTMENT TABLE


***You need to assign new investments to clients, making them active.
YES THEY DONT' HAVE TO BE ACTIVE INITIALLY I CAN OVERWRITE THE
INACTIVE AFTER THE CLIENT APPROVES THE NEW INVESTMENT

***You need to make existing investments for a client "inactive"
after

switching them to a new investment. YES OR AS MENTIONED ABOVE I CAN OVERWRITE IT WITH THE NEW INVESTMENT

***You need to keep a history of a client's investments NO

 

[Guest]

Is this the same subject as your previous post about mutual funds, old fund
and new fund?

 

[ckloch]

yep

 

[pamoori]

Let's talk about the entities, their relationships, and the business need,
not the tables - so I can better understand. Here's what I hear:
**There are many clients, each with attributes
**There are many investments, each with attributes
**A client can have many investments through time; they can switch
investments, making the old one "inactive" and the new one "active".
**A client can have one (or many?) active investment(s?).
**There is an investment ticker but I don't understand this and its relation
to investment

***You need to look up potential new investments for clients.
***You need to assign new investments to clients, making them active.
***You need to make existing investments for a client "inactive" after
switching them to a new investment.
***You need to keep a history of a client's investments

Can you verify or correct these statements?

Hi Bruce:

I like your approach. In fact, I believe that people should think in
terms of atomic facts.
It is not only that, there is a discipline of building data models
from simple sentences.
Towards this< I do have a product which I released recently.

Can I use your discussion in my forum for educating purposes? This
question
applies to cklock, because these problems connect to many designers
style of
thinking.

Please visit www.englishtouml.com/forum. Find the last section on data
base
design problems. I am just collecting some typical examples here. So
when
people read them, they shall feel that, that is my style of thinking.

To resolve those problems, you write simple sentences to view data
models.

With the best regards,
Venkat

www.englishtouml.com
Now UML Data Models Faster And Better