At 09:50 PM 18/08/2007, you wrote:

>Hi to all !
>I develop an application based on stock management,I use VB .Net
>2005 with FB.I also need to keep a stock table based on I/O in month
>(a table) and stock from last month (another table). I'm looking for
>the right way to do it.I'm new with FB, the solved way for me is
>using some .Net datasets (this is based on load needed data from
>server in memory, manage and then send to database for updating.
>),but I think it's not the right way,it is also very slow.

You need to separate your client interface (your applications) from
your data storage. Sure, the applications will be interested in the
data for this month or last month, or indeed quarters, years and
possibly even specific weeks.

To meet these "front end" requirements (which you materialise as
"datasets") you need an abstract structure in the database that
stores enough information without duplicating any of it; and one
that is designed for efficient extraction of the information required.

>I thing it may be another way based only on firebird/stored procedure.

That is a direction you could take *after* you have done a proper
analysis of the data you need to store and how to store it in a way
that makes it easily accessible for the front-end requirements. It
might be a way to get certain datasets that can't easily be extracted
using dynamic SQL, but establish the basics FIRST in anticipation
that most things you need to do with this data can be done with DSQL.

>I/O table : id,price1,price2,quantity,i_o,type

>Stock table id,price1,price2,last_quantity,input,output,type

You need 4 tables: PRODUCT (containing the definition of the items
that might be carried in stock), STOCK_MOVEMENT (containing a record
for each inward and outward movement, similar to your "I/O" table but
it must carry a timestamp and supplier or manufacturing
information!), STOCK_ITEM (carrying quantity records for all of the
products you keep in stock...this might be quite a complex table if
you need to keep separate records for the same product, according to
differentiating attributes...it will be complex anyway, because it is
where you store all the dynamic data about stock as it moves in and
out, gets allocated, packed, reserved, and all the other stuff you
need to record) and STOCK_VALUE (where you store all of the cost and
price data per stock item: the data you store in here might be quite
complex, too, especially if values and prices change from month to
month. This table needs a timestamp too!).

From your examples, it's obvious you need also MOVEMENT_TYPE, a
control table containing the key and description for each type of
stock movement. Each stock movement record will store the
appropriate key for the type of stock movement being recorded.

What you *don't* do is store data for different months in separate
tables. You have just one of each table. Old records can be
archived to history tables when they are no longer relevant to the
ongoing workflow of the business.

>Things are like this : for each record in I/O table I have to :
>1.
> if type = 1
> search in stock for id,price1,price2,type
> if type = 2
> search in stock for id,price1 ,type
> if type = 3
> search in stock for id, ,price2,type
> if type = 4
>
> search in stock for id, ,type

Extracting such info from your stock system will involve joins. When
you write applications, you don't pull the entire contents of your
tables over to the client side: you write your applications in such
a way that the user gets as few records as possible. In SQL you
restrict the records using a WHERE clause. Even for this export you
are doing, you are going to restrict the output to stock movements
within a specified date range. The above query, for example, will
need to join PRODUCT, STOCK_ITEM and STOCK_VALUE and your WHERE
clause will specify Product_ID and a timestamp range.

It's not clear whether you want the above as one set or as a
conditional set. It is not very straightforward either way....and it
does appear that you're not very clear yourself what sort of set you
want. You might use a CASE statement or a UNION in DSQL but it could
be an opportunity to use a SELECT procedure with inputs.

> 2. if not found insert it also update-it this way
> if i_o="O"
> input=input+quantity
> else
> output=output+quantity
> endif
>I have no ideea how to do this with FB

You can (and should) write an AFTER INSERT OR UPDATE trigger to do
this kind of processing. You can refer to the values in the
STOCK_MOVEMENT fields by the NEW context variables and perform
conditional updates/inserts on the related tables. In a real stock
system it will be more complicated than your example because your
tests must test for null as well as other conditions in the
STOCK_ITEM record(s) such as reserved stock and negative stock.

Firebird is a relational database management system and SQL is the
only way to extract datasets from a databases. Stored procedure and
trigger language (PSQL) is SQL too, no VB anywhere.

If this is a student project, grab an SQL primer and a book on the
basics of relational database design from the library and start your
project right there. You can pick up the basics of PSQL from the
Language Reference of the Borland IB6 beta manuals and the rest in
release notes and various papers (refer to the Documentation Index at
the Firebird web site).

Tip: at least half of the RDB design books I've ever sighted use a
basic stock system for work-through examples.

>I need also some advices how to define stock keys

Your PRODUCT table will need a unique key for each distinct product
your system might be interested in. The STOCK_ITEM table will have
its own unique key plus a foreign key to the PRODUCT
table. STOCK_MOVEMENT and STOCK_VALUE will each have its own unique
key plus foreign keys to STOCK_ITEM. STOCK_MOVEMENT will have a
lookup key to MOVEMENT_TYPE, which I recommend you do not make a
foreign key, for performance reasons.

Well, I haven't written your assignment for you but I hope it helps a
little to point you to some of the things you need to study to get
yourself equipped to solve it for yourself.

./heLen