By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Design database

mseo
100+
P: 181
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
Jan 13 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. tblSupplierByProduct
  2.  
  3. SupplierID   ProductID   Price
  4. 101               1        2.56
  5. 102               1        2.15
  6.  
This way you can represent both suppliers and their prices for the product cheese.

Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 13 '10 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 13 '10 #3

mseo
100+
P: 181
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
Jan 15 '10 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 16 '10 #5

mseo
100+
P: 181
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
Jan 16 '10 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. tblSupplierByProduct
  2.  
  3. SupplierID   ProductID   Price
  4. 101               1        2.56
  5. 102               1        2.15
  6.  
This way you can represent both suppliers and their prices for the product cheese.
Jan 17 '10 #7

mseo
100+
P: 181
thank you for your help msquared
now I understand this point
I appreciate you answer
thank you very much
Jan 17 '10 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 17 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.