Question on stock control design

[Ayiramala]

Hi everyone,

I am trying to create a simple, stock control and inventory database. Everything works fine, except for one thing: I have trouble including a table for 'companies'. Right now I have just 'Product_Name' as part of tblProducts, but how do I include a tblCompanies? A company can make several products, each with its own price.



I think it will be easier if you could please take a look at the image of relationships.

Any help is greatly appreciated.

[ranman256]

You need a tCoProd table and the tCo table
tCo has the CoID and the company data,
the tCoProd table has the Co ID and the product and the price:

TCoProd table
---------
CoProdID (auto)
CoID. (From tCo table)
ProdID. (From tProduct table)
Price

this is a child table to tCo.

[Ayiramala]

Thanks. i will include these two and see how it will go.

[ssanfu]

One company has many products
one product has one company
This is a 1-to-many relationship.

Maybe something like this?

[Micron]

I'm curious...
What is "companies" if not the supplier?

one product has one company

This implies that a product can't be made by more than one company/supplier, no?

[Ayiramala]

A company can make several products, a the same time the same product can be made by several companies. So it should be a many-to-many relationship.

My mistake. I should have said it at the very beginning.

By Unit Cost I mean purchase price of a particular item made by a certain company. The same item made by another one may have a different price. Or, the same item made by the same company but purchased at a later time may also carry a different price.

[istari88]

Try a tblCompany related to tblCompanies (1:many) and tblProducts (1:many). When a company is selected then all the products and prices change. And so for each company when selected from tblCompanies.