Table Design Still in Progress

  • Thread starter Thread starter Parts Manager
  • Start date Start date
P

Parts Manager

I am trying to get past this next step in the design, and that is when it
concerns an item to be purchased, yet have a couple of different vendors to
choose from.

tblItem
--------
Id (Our internal part number)
SameAs (Used for like items)
Status (Active/Inactive)
Loc (Location)
tblMfg(Id) (3 Digit MFG code)
MfgItem (Mfg's part number)
Desc (Full part description)
Notes (Any notes we want)
Markup (% Markup for item)
tblVendor(Id) (Preferred vendor)


tblMfg
---------
Id (Mfg Code)
Name (Full Mfg name)







tblVendor
-----------
Id
Name
AddrOne
AddrTwo
City
State
Zip
Acct
Notes
InsideRep
InsidePhone
InsideFax
OutsideRep
OutsidePhone
OutsideFax


tblVitem
---------
Id
tblVendor(Id)
tblMfg(Id)
tblItem(MfgItem)
tblItem(Id)
Price



I put the basic fields in the tblItem table, the Manufactures just have a
short 3 digit code (abbreviation) and then actual long name, Vendor table
has the basic information needed for a vendor, and then I think/thought I
need to have one more table to relate them all together to work properly;
but am unsure.

I want to be able to add an item, pick a 3 digit mfg code, and recommend a
vendor out of the many vendors that may be available; but when I purchase, I
may want to use some other vendor than what I have in the file. For this, I
have not figured out how to handle that yet.

These tables are still in the overall scheme of having a Purchase Order,
Receiving, and Invoicing system. The final will also have a Task/Job table
and Project table that will link into the Hours Worked table. But for now,
I need to figure out the above step on how to get the right vendor at any
time even though many vendors can carry the product, and to put a price
somewhere in this system for which I have chosen the Vitem table because
that tblVitem(Id) is the item number assigned by the Vendor for that file
and therefor the price would be correct for that vendor.

Am I going in the right direction so far with the Price and Vendor per item?

An example is that a 3m product may be sold by 5 different vendors. I may
have a preferred vendor, but occasionally I will add the item to another
vendor's purchase order to make minimum dollar orders or something.

Tim
 
Parts Manager said:
I am trying to get past this next step in the design, and that is when it
concerns an item to be purchased, yet have a couple of different vendors to
choose from.
Click to expand...

If I have a table called tblItem and it has all the basic information on a
part, how do I handle the Mfg, Vendor, & UnitOfMeasure references since the
code/table for them are in another table?

IOW, are the primary key (PK) of those tables placed inside the tblItem so
they are referenced properly?

I am just a bit uncertain if primary keys from other tables are placed in
other tables and are then called Foreign Keys (FK) or in my situation, these
are really LookUp fields and I am handling it all wrong. Any guidance would
be appreciated.

Tim
 
Back
Top