Plant/Equipment Control

[David M C]

I'm designing a database to keep track of which employee has which piece of
equipment. It needs to keep store who has what at any one time but also a
record of who has "hired" a piece of equipment in the past and when it was
returned.

So far, I think it can be done with three tables:

tblPlant:

pkPlantID
PlantName
PlantDescription
Available (might not need this one)

tblPlantHireDetails:

fkPlantID
HireDate
fkEmployeeID
ExpectedReturnDate
ReturnDate

tblEmployees:

pkEmployeeID
FirstName
LastName
etc

Can you see any problems with the table design, especially with regards to
keeping track of which employee has which piece of equipment? What should I
use as a Primary Key in the PlantHireDetails table? I was thinking a
composite key of EmployeeID, PlantID and HireDate?

What if I wanted to give an "OnHire" number (much like an order number) to
group multiple items on hire a single requisition record? This would require
a table between tblPlant and tblPlantHireDetails with a design similar to
that of the Northwind tblOrder and tblOrderDetails. Normalising the database
with this extra table is fairly simple, but keeping the design simple enough
to correctly handle and record the return of equipment has made my head go
blank.

Any ideas or input would be appreciated.

Thanks

Dave

 

[Tom Ellison]

Dear David:

It's not completely clear. Are you setting up so the same employee, with
the same EmployeeID, can be hired repeatedly? This part may be good.

You don't show (as far as I can tell) anything about a table of the
equipment. Where is that?

I assume that each employee can have more than one piece of equipment. Each
piece of equipment can be hired, successively, by several employees. This
seems like a many-to-many relationship. Are you familiar with disigning
that?

Tom Ellison

 

[David M C]

The table with all the equipment is tblPlant. Each employee can have many
pieces of equipment (plant) on hire. Each piece of equipment can only be on
hire to one employee. Basically, I'm trying to keep track of what equipment
we have, who's currently got it, and who's had it in the past.

 

[Tom Ellison]

Dear David:

OK, then. It makes sense. You have the many-to-many setup.

Drop the Available for sure. It can, and must be derived from
TblPlantHireDetails. Every previous hire must be returned before another
can be opened. The item is available when the most recent hire has not been
returned.

The only potentially unique key for Plant is the ID. There is apparently no
unique natural key, or is there? If there are 3 widgets available, how do
you distinguish between them? If two are returned, and one is subsequently
found to have been broken, would you know which employee broke it? Is there
a sticker or manufacturer's serial number to record?

How about employees? If you have 2 John Smiths, how do you intend to tell
them apart? Would there be an Employee Number? Or do you expect them to be
known by an autonumber assigned here? If you have 6 databases, perhaps this
one, a HR database, a payroll database, an expense database, and all of them
assign different autonumbers, will the employee have to remember all the
different numbers by which he is known, and to which purpose each number
applies?

That should do it.

Tom Ellison

 

[David M C]

Many pieces of plant can have the same name and description so I have used an
autonumber field PlantID as the primary key. Each employee is recognised by a
unique EmployeeID. I have a Global backend set-up that I use in all my
database apps. This includes things like employee details, supplier details
etc. The key I'm not sure on is for tblPlantHireDetails. I think a composite
key consisting of HireDate, EmployeeID, and PlantID would be enough.

Could you explain why/where I may have/need a many-to-many relationship? The
way I've currently designed it is:

tblPlant ---> 1:n ---> tblPlantHireDetails
tblEmployees ---> 1:n ---> tblPlantHireDetails

Does this mean there is a many-to-many relationship between tblEmployees and
tblPlant with tblPlantHireDetails being the junction table? My view is that a
single employee can hire many pieces of plant, whilst a piece of plant can be
on hire only once at a time, but can have been on hire many times.

I have also been considering normalising the tables further, since many
pieces of plant can be on hire by the same employee, on the same day.

As such the tables would be redesigned as follows:

tblPlant: (As above)

tblPlantHireDetails:

HireDate
EmployeeID

tblPlantHired:

PlantID
ExpectedReturnDate
ReturnDate

With a one-to-many relationship between tblPlantHireDetails and
tblPlantHired. However, the query design to show who has what becomes more
complex.

By the end of development I require a record of all the plant (equipment) we
have, who currently has it, and who has had it in the past. I would like to
be able to query the database to show every item of plant available, every
item on hire (and who has it), every employee and any plant they have, and
any plant hired by a given employee on a given day.

I then require the ability to return an item from various forms based on the
queries given above. For example, somone may return a hammer drill. In this
case, you may want to put search for the PlantID and return it that way. In
other cases, the employee may bring back several items. In this case, you may
want to filter by employee and return the items they have on hire.

The form design and coding isn't a problem. I just want to make sure the
data structure is designed in a way that will allow everything I've described
above.

Thanks for your input,

Dave

 

[Tom Ellison]

Dear David:

Your tblPlantHireDetails is a many-to-many design. Each employee may have
several plant assigned at one time, or over a period time. Each Plant item
may be assigned to several employees, not all at once, but over a period of
time, consecutively. That's where a many-to-many design is essential.

The hallmark of this design is that the table has a unique key from both the
foreign tables. That's what you did already. This is proper and essential.

The many-to-many relationship is actually a description of a relationship
between tblPlant and tblEmployees. The tblPlantHireDetails is sometimes
called a Junction Table. Imagine a matrix of every row of tblPlant and
every row of tblEmployees. You permit any Plant to be associated with any
Employee. In this case that association is temporary, it is for a period of
time. In effect, time forms a third dimension to this relationship, but
there would probably not be a table for time. It is, instead, a continuum.
It is a universally "understood" element.

As to "further normailiztion" I didn't see where you had any problem. The
properly formed junction table was there. The Plant -> PlantHire
relationship is one-to-many (but unique for any moment in time). The
Employee->PlantHire is a simple one-to-many. That creates the many-to-many
relationship between Plant and Employee.

I'm a bit tired now, and won't try to analyze this more than that for the
moment.

Tom Ellison