Help with relationships

[Guest]

Need help with relationships. This is for a form that hopes to produce a
report for work.
The Tables are as follows:
tblRequest
MISNumber – Number
Requester – Text
Unit – text (this is an area)
Account – Text
OBCode – Text
Description - Text
Justification – Text
Category – Text

tblVendor
MISNumber – Number
Vendor – Text
Address – Text
State – Text
Zip – Text
PhoneNumber – Number
Contact – Text
FedId – Text
ContactNumber – Number

tblCost
Quanity – Number
UnitofMeasure – Text
UnitPrice – Currency
TotalCost – Currency

The tblRequester and tblVendor are easy with the MISNumber, any suggestions
for the tlbCost table. Thanks in advance

 

[tina]

how are requests related to vendors *in the "real" world*? and how is cost
related to either entity, or both - again, in the "real" world? also, what
is the primary key of each table, at this moment? we need to understand the
process you're tracking with this database, before we can make suggestions
for a normalized tables/relationships design.

also, suggest you read up on data modeling. in the final analysis, *you* are
the best person to design your tables/relationships because you know (or
should know) more about the process and the entities involved than you can
hope to tell us in a newsgroup forum. for more information on data modeling
and normalization, see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Guest]

This is to be a Requisition Request. As for the vendor I would like to have a
combobox that when I select the vendor, all the other information about the
vendor will populate the other fields in the vendor table. The request table
is all the other infomation like the unit (area that orders) requester
(person requesting) others like OBcode and Account will come from a
predetermined list of accounts and object codes. The last table cost will
never be predetermined. There are to many types of services (consulting) that
will change from requisition to requisition. When the report is printed, it
will create a form that we would send to our finance and support area for
processing. Hope this helps. I will read the artical you suggested and try
to get things straight. Thank you.

 

[tina]

As for the vendor I would like to have a

combobox that when I select the vendor, all the other information about the
vendor will populate the other fields in the vendor table

well, that vendor data would have to come from "somewhere", so you're kind
of thinking about it backwards. here are my suggestions - but remember that
you know a lot more about your company's requisition process than i do, so
the following is *not* a substitute for you learning the basics of data
modeling and applying those principles to your database design process.

tblVendors
VendorID (primary key, probably Autonumber data type)
Vendor - Text
Address1 - Text
Address2 - Text
City - Text
State - Text
Zip - Text
PhoneNumber - Text
Contact - Text
FedId - Text
ContactNumber - Text (i assume this is a phone number field, so make it Text
data type rather than Number data type.)
< this is a list of vendors. each field in this table should describe an
attribute of a specific vendor, and nothing else. >

note that if you may have more than one contact person for a single vendor,
then you should remove the Contact and ContactNumber fields from this table,
and create an additional table:

tblVendorContacts
ContactID (primary key, probably an Autonumber data type)
VendorID (foreign key from tblVendors, Long Integer)
Contact - Text
ContactNumber - Text

okay, next, the fields in your tblCost suggest that "things" will be
requisitioned, as well as consulting services. so i'll presume that more
than one thing may be requested on one requisition. in that case, you'll
need the following tables:

tblRequisitions
MISNumber - Number (i'll assume that the MISNumber is unique to each
requisition, so use it as the primary key. if it's NOT unique, then add a
separate primary key field of data type Autonumber.)
Requester - Text
Unit - text (this is an area)
Account - Text
OBCode - Text
Description - Text
Justification - Text
Category - Text

after Requestor, the rest of the above fields - from Unit to Category - are
questionable. any field that applies to a specific item being requisitioned
needs to be in the following table. any field that applies to the
requisition as a whole should remain in the table above.

tblRequisitionDetails
DetailID (primary key, Autonumber)
MISNumber (foreign key from tblRequisitions)
Quanity - Number
UnitofMeasure - Text
UnitPrice - Currency
(note that i did NOT include a TotalCost field. total cost is a calculation
of Quantity times UnitPrice; since you are storing the raw data necessary to
calculate the total, you should NOT save the total as data - just calculate
it whenever you need it in a query, form, or report.)

as you can see, to make a firm decision on how to correctly structure the
tables, you need a thorough understanding of the process - which i don't
have. so use the above design as an example only, read up on data modeling
and normalization, analyze the process you need to track to be sure you
understand it thoroughly, and design your tables/relationships from there.

hth

 

[Guest]

Thanks for all the help, just one more question. In the relationships I used
the Vendor ID between the tblventors and tblvendorcontacts, and the MSINumber
between the Requisitions and the tblrequisitionDetail. Don't I have to
somehow relate one of these tow relationships with the other. thanks in
advance

 

[tina]

yes, presumably each requisition will be filled by one vendor. so put the
VendorID field in tblRequisitions as a foreign key. if, on the other hand,
each *item* in a requisition may be filled by a different vendor, then put
the VendorID field in tblRequisitionDetails as foreign key, INSTEAD OF in
tblRequisitions. in either case, VendorID is the *only* field that should be
added to the other table; do NOT add any other vendor fields.

again, the above is an example of your need to understand the requisition
process in your company in order to determine the correct table structure.

hth