Design database

[mseo]

hi, everybody
I need to design database for export & import company
the first problem which I have met is:
I want to design the tables and make the relationships to make it like [u]more than one supplier and more than one price for the same product[/U
this the first problem
the second one: what about if I have seven suppliers and therefore 7 prices how can I select the 3 cheapiest price in the developing level
I will be so happy if I get any help
and thanks in advance for any help you may provide me

[MMcCarthy]

I suggest you read this article on Database Normalization and Table Structures. You will need to get a better understanding of how databases work. Then once you have your basic table structure in place post the structure and we will try to help you sort out any relationship problems you are having.

[nico5038]

Having multiple prices demands to have a "unique" field that separates them. Looks to me you need to have prices per supplier so the table will need at least:
SupplierID, ProductID and Price

In general Prices can differ over time so you might need to keep track of the price history and thus in need of a (start and end?) date.
As an alternative you could store the price in an invoice when created to keep another kind of history of prices. This depends on how the company handles price changes.

The three cheapest can be selected by a "SELECT TOP 3" query that's being sorted on an ascending price.

So something to start with and be sure you ask the company many questions about how there procedure for Orders and Invoices is organized.

Nic;o)

[mseo]

thanks for the previous replies
the attached file is the design which I started with and I ask you for any adjustments to make it suitable for the Rule: More than one supplier with more than one price for the same product.
I designed tbl_tenders for make it as I need but I think with this design I would duplicate any record
for instance if the table contains three fields
supplier_ID Product_ID Unit Price
A Co. F 2 $
I need not to duplicate this record on the level of supplier_ID and Product_ID in any record Just I want to edit the price not to enter them again to make it easy to me
and as kind of request I want the design not to be complicated because I will make the developing phase on my own. and I want to develope this program without confronting a lot of error resulting from the designing of the database
thanks for any help you may provide me with

[nico5038]

the Rule: More than one supplier with more than one price for the same product.

This isn't precise enough defined.
When one supplier can have multiple prices for one product (e.g. different price lists), an additional key field will be needed. You could add a "PriceListStartdate" field in the tblTenders, but that will also require that the price is collected using a date...
The UnitPrice shouldn't be in tblProduct as it's aready in the tblTenders, just the unique identifier(s) of tblTenders needs to be stored when using the product in an Invoice or Order detail.
When you don't keep a history of prices in tblTenders (so no multiple prices for one product of one supplier) you'll need to store the UnitPrice in an order or invoice detail. When you keep multiple prices per product, than the order/invoice date will be required to get the proper UnitPrice.

Getting the idea ?

Nic;o)

[mseo]

thanks for you reply
but I didn't mean more one supplier more than price for one product
I meant more than one supplier so more than price for one product
let's assume that the cheese is the product and there are more than one supplier for this kind of cheese and those suppliers have different prices for the product
that is what I meant
how can I make the design suitable for this without duplicating any record
thanks

[MMcCarthy]

I wouldn't put the price in the product table. I would create a join table between supplier and product i.e. SupplierID and ProductID as the joint primary key in another table and add the price field to that table. See below for example with Suppier A having an id of 101 and Supplier B having an id of 102 and then product cheese having an id of 1.

 tblSupplierByProduct
 
 SupplierID   ProductID   Price
101               1        2.56
102               1        2.15
 

This way you can represent both suppliers and their prices for the product cheese.

[mseo]

thank you for your help msquared
now I understand this point
I appreciate you answer
thank you very much

[MMcCarthy]

You are welcome.

To explain further you always have to do this when there is a many to many relationship. A supplier can have more than one product and a product can have more than one suppier. This is what's known as a many to many relationship.

The join table allows you to have a new table which has only a one to many relationship with the other two tables. You then add any fields to this table which are unique to the joint relationship like price.