Tables for tracking Status 1

[jw45]

I have to keep track of an item through a sign-off process. When we get an new order for a item, we want send it through all departments for approval and keep track of the who/when it was completed.


Fields that I need are:
Item
DateCreate
EngAppToSchedUser (Engineering Approval to Master Schedule)
EngAppToSchedDate
QualityUser
QualityDate
FinanceUser
FinanceDate
MaterialsUser
MaterialsDate
CheckListReqd
CheckListStatus
EngAppToProduction (Engineering Approval to Release to Production)
EngAppToProductionDate
ToolingUser
ToolingDate


Is it Ok to put all this in one table or do I need multiple? If multiple, how do I need to structure?

I have read several suggested posts on Tek-Tips about the fundamentals of relational database design but I am still a little confused.

Will some one help me get started?

Thanks
JW

 

[lespaul]

So it appears that you have ITEMS and APPROVALS....I would think that you would only need the two tables. The APPROVALS table should contain a foreign key to the ITEM that is being approved and the type of approval and who/when the approval occurred:

ITEMS
ItemID (PK)
DateCreated
(any other pieces of information about the ITEM - name?, etc.)

APPROVAL
ApprovalID (PK)
ItemID (FK to ITEMS)
ApprovalType (Tooling, Quality, Finance)
UserName OR UserID (depending on where this information is stored)
ApprovalDate




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[johnherman]

Leslie is right. Ideally, you would have two tables. That makes it easier if you need to add or remove an approver.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dhookom]

Leslie is so right that she deserves a star.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]