Re: Questions about my strategy

On Wed, 2002-07-31 at 22:47, Nigel J. Andrews wrote:

Now you have me confused and paranoid, but I learnt something new so
thats good (the constraint when creating a table).

> That structure gives you everything you want whether count is a difference or
> an absolute, at least I think it does. I believe it's also more of a
> correct solution than the convoluted way you seem to be thinking. Some database
> expert will now correct me on those points. :)

What I am doing is I thought what you just presented, from pg_dump I
have:

CREATE TABLE "stock_products" (
"loc_seq_pkey" text DEFAULT set_unique_key() NOT NULL,
"timestamp" timestamp with time zone DEFAULT 'now()',
"version" integer DEFAULT 0,
"f_new" character varying,
"f_update" character varying,
"product" character varying,
"category_key" text,
"brand_key" text,
"sizegrp_key" text,
"code" character varying,
"biz_key" text,
"value" numeric(12,2),
Constraint "stock_products_pkey" Primary Key ("loc_seq_pkey")
);

CREATE TABLE "stock_transactions" (
"loc_seq_pkey" text DEFAULT set_unique_key() NOT NULL,
"timestamp" timestamp with time zone DEFAULT 'now()',
"version" integer DEFAULT 0,
"f_new" character varying,
"f_update" character varying,
"product_key" text,
"branch_key" text,
"size_key" text,
"colour_key" text,
"transaction_key" text,
"type_key" text,
"count" integer,
"value" numeric(12,2),
Constraint "stock_transactions_pkey" Primary Key ("loc_seq_pkey")
);

all the *_key fields link to other tables, I do this so that I can
easily add colours etc...

product_key likes to the stock_products table. count is the number of
titems in the transaction, negative for stock leaving, pos for
incomming, type_key is type of movement, eg: sale or transfer to another
branch etc. Value is the value of the transaction at that point in
time. I keep this as next month the shos might have cost the store more
than this month and is taken from teh stock_products table.
Transaction_key is so that I can group them togeather, ie: a link to a
stock_order table (ties in with type,ie if type is ionwards then links
to orders table).

--

*
* Rob Brown-Bayliss
*