Trouble with composite keys

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Would appreciate your help and inputs on my database design. I am desinging
an inventory system. Each item is based on unique project and i need to
account for items received and issued.

Tables:

tblprojects
projectID - autonumber
pno-project no. - pk
pname-project name
(All project nos are unique)

tblitems
itemID-autonumber
itemno-Itemno-pk - text
prno-projet no -pk - FK for project table. - numeric
itemDesc-itemdesc
uom- unit of measure

(Here i have created a composite key for itemno and project no because in
combination they are unique.)

tbtransactions
TransactionID
PID-Project no - FK
Itemno- item no -FK
DOR-Date of Receipt
UnitsReceived - Qty recieved
DOI - Date of Issue
UnitsIssued - Units issued
loc - location

(the pid and itemno are foreign keys pointing to tblitems)

I have a combo box in my form bound to tblprjects when i click on project i
get the required itemno in combo box itemno. when i click on the item no I
should get the related feilds from tbltransaction but i am getting unrelated
records.

I dont think i have my relationship set right. Can anyone helpl me. I do not
know how to send the relationship map through the news groups
thanks in advance
 
Hi -

Can items with the same description (itemDesc) be used in different projects?
If so, then your data is not properly normalized. You need to remove the
project number from the tblitems, because it is your tbltransactions table
which relates the items to projects.

If any given item is used in only one project, then yes, you can keep the
project number in tblitems, but you have to remove it from tbltransactions,
because in by specifying the item number in tbltransactions, you are implying
the project number too. I don't like this approach though, because in the
event an item *does* get used in two or more projects this data structure
won't work.
I have a combo box in my form bound to tblprjects when i click on project i
get the required itemno in combo box itemno. when i click on the item no I
should get the related feilds from tbltransaction but i am getting unrelated
records.
Click to expand...

You question suggests that each project has only one item associated with it -
is this really the case?

Once you sort out the data structures it should be easier to set up your
queries and forms.

John
 
Tables:

tblprojects
projectID - autonumber
pno-project no. - pk
pname-project name
(All project nos are unique)

tblitems
itemID-autonumber
itemno-Itemno-pk - text
prno-projet no -pk - FK for project table. - numeric
itemDesc-itemdesc
uom- unit of measure

(Here i have created a composite key for itemno and project no because in
combination they are unique.)

tbtransactions
TransactionID
PID-Project no - FK
Itemno- item no -FK
DOR-Date of Receipt
UnitsReceived - Qty recieved
DOI - Date of Issue
UnitsIssued - Units issued
loc - location

I dont think i have my relationship set right.
Click to expand...

I think you are missing a relationship table (a.k.a. junction table,
etc) to model the valid (and unique) combinations of projects and
items, presumed a many-to-many relationship. I've named this
relationship table ProjectInventories in my suggested revision (I've
tried to follow your notation <g>):

tblprojects:
pno - PK
pname

tblitems:
itemno - PK
itemDesc
uom

ProjectInventories:
pno - FK REFERENCES tblprojects
itemno - FK REFERENCES tblitems
- PK (itemno, pno)

tbtransactions:
TransactionID - PK
pno
itemno
- FK (itemno, pno) REFERENCES ProjectInventories
UnitsReceived
DOI
UnitsIssued
loc

Jamie.

--
 
Back
Top