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
*