Tables :: How To Incorporate Selling A Product Option

You have a company that sells a small line of very exotic, non-perishable, chemicals to manufacturers.

You source these chemicals from around the world and resell them to the manufacturers.

The chemicals are not only very expensive, but may or may not be required for any given batch of product the manufacturer is producing.

The manufacturer does not know if the chemicals will be required, or not, until the last minute, so must have them immediately available for each batch.

You keep track of the process with a simple database that is built around a "Chemicals" database and a "Transactions" database. Which are linked by [Product Code]

Quite straight forward to this point.

-----------------------------------------------------------------------------------------------

Now

You are seeking a way to make these chemicals accessible to manufacturers who cannot afford to buy them, just to have them sit around waiting for the day they are needed.

You set up a system where a manufacturer can purchase an "Option" on the chemicals whereby:

They pay you a certain amount of money for the right to hold the chemicals at their plant for a given period, denoted in weeks.

After the pre-determined period is over they must either:

Return the chemicals to you and you keep the money they paid for the option.

If they used the chemicals, pay you the pre-determined purchase price --you still keep what they paid for the option.

----------------------------------------------------------------------------------------------

My problem is incorporating the options into the database built around "Chemicals" and "Transactions," e,g,:


(1) The "Chemicals" table has fields such as [Product Code],[Quantity in Stock],[Cost],[Sales Price per Kilo]. The options require a unique set of fields such as [Product Being Optioned], [Expiry Date of Option],[Sales Price per Kilo -If Used],[Weekly Price for Option]

It doesn't seem correct to jam these into the same "Chemicals" table although I could be wrong.

(2) The same is true for the "Transactions" table, i.e., it now identifies the chemicals in each transaction using [Product Code].

To identify an option in a transaction one needs to use [Product Code] and [Expiration Date of Option]

(3) When a chemical is optioned the company has to track the fact that that quantity of it is not available for outright sale until the option expires

(4) A further problem exists even if I have figured out how to structure the tables.

Before selling a chemical one must create a record for that chemical in the "Chemicals" table--not a problem.

Options on the chemicals, however, do not exist until they are sold i.e., instead of using the "Chemicals" table to look up data for each transaction. Each transaction (to sell an option) has to create a record in the table being used to track the options