firebird-support - Re: [firebird-support] Righ way of doing that ?

Subject	Re: [firebird-support] Righ way of doing that ?
Author	Mr. John
Post date	2007-08-18T16:37:45Z

Thanks Helen for your advices,I'll use them.Thanks again.

----- Original Message ----
From: Helen Borrie <helebor@...>
To: firebird-support@yahoogroups.com
Sent: Saturday, August 18, 2007 7:15:48 PM
Subject: Re: [firebird-support] Righ way of doing that ?

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



____________________________________________________________________________________
Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow

[Non-text portions of this message have been removed]