hello,
I am making an online stock brokerage, now what i need to do is store
values in transaction table of stocks bought thru jsp. what i am not
able to figure is say i allow five stocks to be bought then when a
buyer buys a stock how do i number them in database,
say i have five columns for stockname in table stockinfo named
stockname1, stockname2, stockname3.......stockname 5.
Now a customer buys a stock and i want to insert stock where stockname
is null how do i write code for this in jsp.
if stockname 1=null
insert....
else if stockname2 =null
insert....
thanks
The database design you are envisioning would be very poor. Your proposed
design would involve what database designers call a "repeating group"; this
is a very poor choice for a design.
A much better design would be a table that stores a separate row for each
stock. This simplifies the table, makes most queries much easier and removes
the limit on the number of stocks so that you could store 5 or 500 or 5
million if you wanted.
For example, assuming you wanted to associate the stock with a specific
customer, you could have a design like this:
StockTable
=======
CustomerNumber Stock Quantity
-------------------- ------- ---------
123 IBM 5000
123 MSFT 250
123 ABC 375
In this design, there is no need to number the sequence of the stock
purchases at all. However, you could still number them if you like.
You could have a simple integer counter that got incremented by 1 for each
different stock that was purchased:
CustomerNumber Stock Quantity Sequence
-------------------- ------- --------- -----------
123 IBM 5000 1
123 MSFT 250 2
123 ABC 375 3
Many databases have a way to automatically generate the sequence numbers and
store them in the table for you.
It might be more useful to keep track of the sequence indirectly, by storing
the timestamp that showed when the stock was purchased:
CustomerNumber Stock Quantity PurchaseTimestamp
-------------------- ------- --------- ------------------------
123 IBM 5000 2006-03-11 10:25:05
123 MSFT 250 2006-03-12 14:22:19
123 ABC 375 2006-03-13 09:00:00
The timestamp indirectly shows you the sequence in which the stocks were
purchased, in case you care about that, and also tell you exactly when it
was purchased. Many databases can automatically generate the correct
timestamp for you when you insert the rows.
Any of these approaches would be a much better design that storing several
stocks in one row.
