Need understanding with relationships with my database

[Technyc2003]

I'm trying to create a rather simple database that I will be linking with Crystal Reports.

My task is to create a Vendor tracking system for each department within my company.

For example, we have 15 different departments in 15 different locations.

We have to pay various utilities bills for each of those 15 different site. Those vendors would be electrical company, telephone company, oil delivery company, office supplies, etc..

Here's how I structured my tables so far:

tblDepartments
--------------
DeptID (PK)
DeptName
DeptAddress
ContactPerson


tblVendors
-------------
VendorID (PK)
VendorName
VendorAddress
VendorContactPerson
DeptID

tblBilling
--------------
BillingID (PK)
VendorID
BillPostDate
BilledAmount


Now I'm not sure if my tables are structured correctly but if they are, do I link the following fields to each other in the relationships screen?


tblDepartment = DeptID TO tblVendors = DeptID (1 to Many)

tblVendors = VendorID TO tblBilling = VendorID (1 to Many)


My forms is a simple one that I have yet to create but somehow I picture it as the following.

Combo Box = Department Name
Fields = Location, Contact Person, etc...

subform in datasheet view

Vendor Name (Combox Box hopefully to look up the Vendors I already typed into the tblVendors)

Bill Date
Bill Amount

that's it.

I don't know since I already have the Vendors name in the table if I should be storing them someplace else or not. Just curious to know if I'm on the right track or not.

Any help will be greatly appreciated.

Thank you.

 

[lespaul]

I would have to say that I would put the DepartmentID in the Billing table, not the vendor table....that way if you have 2 different locations with the same Electric company when you have the bill you can see which department it belongs to...unless you are planning on having TWO vendors with the same information just differentiated by which department they "belong" to....

My 2¢


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Technyc2003]

There will be only 1 Electric Company, there are various Phone Companies, that's why I figured I'd put their various names in the Vendors table.

BTW: is my relationship okay that I posted?

 

[fneily]

See Fundamentals of Relational Database Design

http://r937.com/relational.html

LesPaul is right, DeptID doesn't belong in the tblVendors but tblBilling. The third normal form talks about this. Look at your tblVendors. For each non-primary key field, such as Vendorname, does it relate to the primary key, VendorID? Well, yes. So it stays in the table. Does DeptID relate to VendorID? No. DeptID has absolutely nothing to do with anything about a Vendor. So it doesn't belong there.
Now tblBilling is what is known as a junction table. It can have its own primary key, the primary keys of the other two tables as just regular fields, and any COMMON DATA. So tblVendors and tblDepartments can be pre-filled and your input form will be based on tblBilling. DeptID and VendorID can then be comboboxes bound to their tables.