simple database help

[Yogesh Nath]

I would like to build a simplae office stationary database.

At present when a staff takes items from the receptionist, the receptionoist keeps a track of it. Below is what she keeps track of:

Date taken:

Taken by:

Department: ( we have 8 departments such as Audio, Video, Graphics etc)

Item Taken:

Quantity:

Purpose:

I have come up with the following tables and fields:

1) Department Table:

Department_id (pk)

Department_name

2)User Table

User_id(pk)

Department_id (fk)

User_name

3) Item Table

Item_id(pk)

User_id(fk)

Date_taken

Quantity

Purpose

Department

I am not sure about this relationship. Is this correct? Could anybidy provide any help please.

Thank you heaps for your time.

Yogesh

[Fitch]

That's looking pretty good. What you're calling "Item Table" I might rename "Tracking" or something, so I could use the name "Items" for an actual list of available items. Does that make sense?

[The Shadow]

I would modify the last table to use the department_id also, not the department name. You could consider not having it at all (since user_id implies it), but I suppose users could switch departments, so it might be good to just fill the department_id field in with a lookup at the time an item is taken.

3) Item Table

Item_id(pk)

User_id(fk)

department_id (fk)

Date_taken

Quantity

Purpose

[Yogesh Nath]

Wow, thank you Fitch and Shawn for your so valuable comments.

Could I please ask another question.

Here is my Items Table

Deprtament: (lookup)

User: (lookup)

Item Name

Item Description

Quantity

Purpose

When I use a department say audio, in the User field, I would only like to show the users from that department. How is this done? Its done somehow through self joing tables but not quite sure.

Also, perhaps it will be a good idea that Item Name and Description is also a lookup. Could somebody point me in the right direction please.

Thank you for all your help.

Yogesh

Edited February 20, 2008 by Guest

[The Shadow]

If you are going to have more information about the Item, then you should separate the Tracking from the Item too.

Related valuelists can be used to give only the users in the department as choices (as long as the department is entered first).

Here's a quick & ugly example of how I would set this up - notice you can make it look nicer by stacking the related field names on top of the id fields, but that makes it more confusing to start with.

ItemTracking.fp7.zip