Simple ERP problem

[Leszek Dubiel]

-----------------------------------------
BACKGROUND

In my company (www.glass.biz) we use ERP software
to compute what has to be done to do products for
our customers. Main algorithm takes data in form
of two tables -- let's call them "Structure" and
"Orders".

"Structure" keeps information on construction
of our product -- what resources we need to
produce a product, and how many pieces of that
product we make at once. For example table that
looks like:

do_product_A resource_X -3
do_product_A resource_Y -5
do_proudct_A product_A +2
do_product_B resource_X -7
do_product_B resource_Z -1
do_product_B product_B +1

tells that to produce 2 pieces of product_A
we need to use 3 pieces of resource_X and
5 pieces of resource_Y. To produce product_B
we use 7 pieces of resource_X and one piece
of resource_Z. Names that occur in table I
would reference as "identifiers".

"Orders" is a table that keeps information
about current orders, and could take form
like this

product_A 4
product_B 2.

Our program computes that we need to
perform task do_product_A two times,
and perform task do_product_B two times
also, so we need

resource_X 20
resource_Y 10
resource_Z 2.

What was shown above is simplified view
of our model, but shows all problems I
would like to discuss.

Our software works fine iff:

1. Identyfiers are strings that
match case insensitive perl regex
described by pattern: /[A-Z](_?[A-Z0-9])*/.
This means that identifier starts with
a letter, ends with a letter or digit,
and can contain uderbars. No two underbars
can be next to each other. Underbars
can't be at the beginning and at the end
of identifier.

2. Identyfier can't occur in both columns of
"Structure" table (even in different rows).

3. Pair consisting of identifier in the first column
and identifier in the second column should be
uniqe in "Structure" table.

4. Identifiers in "Orders" table must be
present in the second column of
"Structure" table.

-----------------------------------------
PROBLEM and my SOLUTION

For realiability and multiuser purposes I
want to move our system to PostgreSQL, but
I found some problems which I describe below.

1. To check identifiers I would use ~* operator.
Will it work with regex shown above?

2. Identifier can't occur in both columns, so
I will make multicolumn Primary Key on first
two columns of "Structure" table.

3. How to I prevent from inserting same identifiers
in first and second coulumn, for example:

do_something XXX -4
XXX resource_Y +3

4. How do I prevent from inserting in table "Orders"
identyfier that is not present in the second
column of "Structure" table? I could use Foreign Key
in "Orders" table, but I don't need first column
of "Structure" table, so this part of Foreign Key
would always be NULL.

Thank you for any useful reply and opinions.
Problem seems to be simple, but I am completely novice,
so can't solve it on my own behalf.

Leszek Dubiel