455,071 Members | 1,188 Online
Need help? Post your question and get tips & solutions from a community of 455,071 IT Pros & Developers. It's quick & easy.

# Table 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