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

jmDesktop

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

Ed Robichaud

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

jmDesktop

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

pietlinden

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