By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

DB Structure help

P: n/a
Hi All,

I am working on a cars investment tracking database and need some help
with structure

You have investors, who pass the money to company the money in turn
purchase cars, sell, and return profit plus capital to investorsy,

Money wise I require tracking by Investory and Also by each car,

For each car, I have various fields in car_details table, which capture
this information e.g. what amount invested, by which investor, what are
the expense, sale price and than profit.

I am now working on tracking by Investor

e.g. Investory A has lended 50,000 $ to company, this money may be used
against 5 cars @ 10,000 each, after three months the cars are sold and
we have 5,000 Profit, and investor is returned 55000. This is the
simplest transaction,

However, the captial may be invested again, the profit may also be
invested, during this time the investory may pump in more money.

I have come up with structure ike this.

Investory Table _ = PrimaryKey, Investory Name
InvestoryDetailsTable = Investory_Foriegn Key, than I have IN, OUT,
INVESTED, PROFIT

This is the detail table and as a first transcation I may have 50000
In, rest all fields set to Zero
Second transacation may be Invested 50000 (in 5 cars), in this case
Invested field will be used
Third tanscation may be Profit ented as 5000 rest of the fields Zero
and so on.

If I want to know the total Captial Due to invesntory I can sum take
Sum of all colum and Do IN - OUT + PROFIT gives me NET DUE

If I want to FREE CAPITAL of Investory at any time of any investnory I
can do In - Out - Invested + Profit.

This is my current stuture, I would like to know

a) Right now there is duplication of entry, that I am entering Lum Sum
Money in the InvestorDetailsTable e.g. 50000 received and I have to do
manual entry in the Cars details table e.g. 10000 each was assigned to
5 different cars, Is there a way I can avoid this
b) Is the above table struture the best way of doing this?

Thanks you for your time. Regards, Irf.

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ir**@emirates.net.ae wrote:
[snip]
This is my current stuture, I would like to know

a) Right now there is duplication of entry, that I am entering Lum Sum
Money in the InvestorDetailsTable e.g. 50000 received and I have to do
manual entry in the Cars details table e.g. 10000 each was assigned to
5 different cars, Is there a way I can avoid this
b) Is the above table struture the best way of doing this?

Thanks you for your time. Regards, Irf.


Sounds like you need to take the money out of both tables and put it in
a new table along with foreign keys to the other tables. You also may
have a many-many relationship between investors and cars which suggests
you need a joining table for those as well.

IOW,

===== You have

Investors
---------
ID
Money

Cars
----
ID
Money
===== Make this

Investors
---------
ID

Cars
----
ID

Investors-Cars
--------------
ID
Investors.ID
Cars.ID

Money
-----
ID
Investors-Cars.ID
Amount
Does this help?
--
Smartin
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.