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

Database Design Vendors and Items Question. Do I need a third table?

P: n/a
Hi, probably a simple question that I have for what I think is a simple
database. I have a Vendors table and an Items table. There is only
one items, but several Vendors may have that same item. I have to have
a third table, correct, to connect them?

I was thinking

OrderDetails (or something like that)

OrderDetailID
ItemID
VendorID
ItemSerial (I thought about this because each vendor might have the
same vendor, but different serial numbers, which I cannot put in the
items table)
Price
Purchased (y/n field)
Comments

I can then select all items and the vendors or a vendor and all the
items they sell. I really just need this simple thing. I don't need a
full-fledged inventory program. We don't need to track it like that.
It's more for initially purchasing. Sort of a furniture and fixtures
type database, but without the need for full asset management.

I thought that if I had just the two tables and put in say ItemID in
the Vendor table that I'd have duplicate rows. I'd have to type in the
vendor name each time.

Does this sound right? Thank you for any help.

Jan 22 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Look at the "Northwinds" example and/or the Inventory template that ships
with Access. These order/inventory/sales data designs have been worked out
for you. Copy and adapt them to your needs.

-Ed

"jmDesktop" <ne***********@gmail.comwrote in message
news:11**********************@s34g2000cwa.googlegr oups.com...
Hi, probably a simple question that I have for what I think is a simple
database. I have a Vendors table and an Items table. There is only
one items, but several Vendors may have that same item. I have to have
a third table, correct, to connect them?

I was thinking

OrderDetails (or something like that)

OrderDetailID
ItemID
VendorID
ItemSerial (I thought about this because each vendor might have the
same vendor, but different serial numbers, which I cannot put in the
items table)
Price
Purchased (y/n field)
Comments

I can then select all items and the vendors or a vendor and all the
items they sell. I really just need this simple thing. I don't need a
full-fledged inventory program. We don't need to track it like that.
It's more for initially purchasing. Sort of a furniture and fixtures
type database, but without the need for full asset management.

I thought that if I had just the two tables and put in say ItemID in
the Vendor table that I'd have duplicate rows. I'd have to type in the
vendor name each time.

Does this sound right? Thank you for any help.

Jan 22 '07 #2

P: n/a
Thanks. It looks like with at least one example from MS that I need a
PurchaseOrder table that has a poid, supplierid, and an employeeid. In
my database, I think I just need to change employeeid to itemid. But I
will try and find the Northwind database.

Ed Robichaud wrote:
Look at the "Northwinds" example and/or the Inventory template that ships
with Access. These order/inventory/sales data designs have been worked out
for you. Copy and adapt them to your needs.

-Ed

"jmDesktop" <ne***********@gmail.comwrote in message
news:11**********************@s34g2000cwa.googlegr oups.com...
Hi, probably a simple question that I have for what I think is a simple
database. I have a Vendors table and an Items table. There is only
one items, but several Vendors may have that same item. I have to have
a third table, correct, to connect them?

I was thinking

OrderDetails (or something like that)

OrderDetailID
ItemID
VendorID
ItemSerial (I thought about this because each vendor might have the
same vendor, but different serial numbers, which I cannot put in the
items table)
Price
Purchased (y/n field)
Comments

I can then select all items and the vendors or a vendor and all the
items they sell. I really just need this simple thing. I don't need a
full-fledged inventory program. We don't need to track it like that.
It's more for initially purchasing. Sort of a furniture and fixtures
type database, but without the need for full asset management.

I thought that if I had just the two tables and put in say ItemID in
the Vendor table that I'd have duplicate rows. I'd have to type in the
vendor name each time.

Does this sound right? Thank you for any help.
Jan 22 '07 #3

P: n/a

jmDesktop wrote:
Hi, probably a simple question that I have for what I think is a simple
database. I have a Vendors table and an Items table. There is only
one items, but several Vendors may have that same item. I have to have
a third table, correct, to connect them?

I was thinking

OrderDetails (or something like that)

OrderDetailID
ItemID
VendorID
ItemSerial (I thought about this because each vendor might have the
same vendor, but different serial numbers, which I cannot put in the
items table)
Price
Purchased (y/n field)
Comments

I can then select all items and the vendors or a vendor and all the
items they sell. I really just need this simple thing. I don't need a
full-fledged inventory program. We don't need to track it like that.
It's more for initially purchasing. Sort of a furniture and fixtures
type database, but without the need for full asset management.

I thought that if I had just the two tables and put in say ItemID in
the Vendor table that I'd have duplicate rows. I'd have to type in the
vendor name each time.

Does this sound right? Thank you for any help.
Here's a grossly simplified design (you can add fields all you want).

Vendor(VendorID (PK), VendorName...)
VendorItem(VendorID (PK), ItemID (PK), Price)
Item(ItemID (PK), Description)

It's a classic Many-to-many. which means you need a junction table
(VendorItem) that links the Vendors to the specific items in the case
that one vendor sells many items, and may vendors may sell the same
item.

Vendor----(1,M)----VendorItem----(M,1)----Item

HTH,
Pieter

Jan 22 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.