Tables and Relationships - Simple Question

[Neutralizer]

Hi All,

I have a quick question that I hope you can help me with.

Situation: I'm creating a database to keep track of earth moving activities. There are multiple operators (people operating the earth moving equipment).
There are also multiple pieces of equipment.
In any given shift, multiple operators may use one piece of equipment.
Similarly, one operator may use multiple pieces of equipment.

Question: What is the best way to structure my database?

I'm thinking that I should create a table for all the operators, and then a table for all the equipment and establish a 'many to many' relationship between the primary key of the equipment (the equipments ID number) and the primary key of the operators table (the operators employee number).

Is this a valid structure, and is it one that can be implemented in Access?

Thanks

 

[jackd]

What do you want to do with a Many to Many?

You have the following entities

Operators
Equipment
Shifts

As you said

1 Operator can operate/use 0,1 or more pieces of Equipment in a Shift
1 Equipment(piece of) can be used by 0,1 or more Operators in a Shift

How will you identify which Operator uses which piece of Equipment during what time period?

In order to resolve the Many to Many (Operators and Equipment), you would need a junction table. This table would uniquely identify an Operator /Equipment combination.

eg: Joe operates a Back hoe

In your Operators table, you have info related to Operators. Things like
Name, Address, SSN,... and an identifier OperatorId which uniquely identifies that Operator from all others.

typical record : Joe Smith, 123 Apple St., SomeCity, StateX,,34353......231

In your Equipment table you have info related to Equipment only. Things like
Equipment name, Brand, Year Manufactured, .... and a unique piece of equipment identifier. EquipId uniquely identifies that Equipment from all others.

typical record : Back hoe, John Deere,2004..... 7711

In your junction table, say tblOperatesEquip you want to identify the Operator (Joe Smith) and the Equipment (Back hoe). So this table would contain the unique OperatorId for Joe Smith (231) and the unique EquipId (7711).

Also, in your junction table would be any info specific to the combination of Operator/Equipment. This could include the start and stop time of this usage.
It could identify any sort of issue/problem that occurred (breakdown etc).

In my view the junction table should have a separate unique identifier (PK) for ease of reference and completeness. I would use autonumber type field.

Also, in your case, you will probably include the shift identifier in your junction table. The junction table could be a junction of 3 tables--Operators,Equipment and Shift. I didn't include the Shift Identifier in the table, but it could/should be there. How would you account for Joe using the backhoe at different times in the same shift --If another Operator uses that Back hoe during the same Shift? You will probably have some shift breakdown (15 minute/half hour???) to allow for multiple operators of same equipment during a given shift.

Hope this is helpful.

Good luck.

 

[Neutralizer]

Excellent information, thanks for that. I'll have a play around and see what I can come up with, thanks again.

 

[Neutralizer]

For the reference of other newbs, I believe an alternative method to using a junction table which is avaliable in Access 2007 and onwards is a multivalued field lookup:

http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx

 

[Norie]

I think you'd be better off using a junction table.

I've never actually heard multi-value fields being callled an alternative.

Actually most of what I've seen about these fields is when people are having trouble with them.

You'll probably find a few threads on the subject here.

 

[Neutralizer]

Ah okay, I might be wrong. Based on the explanation I saw, it implied that a multi-value field lookup created a hidden table (i.e. a junction table) for the purpose of establishing many to many relationships.

I can't say I've tried it, I've built my own little junction table as it's a good part of understanding how databases really work, and I've never liked the black box approach to making things happen...

 

[Norie]

I think some sort of junction table is created but it's not accessible to the user, which isn't really much use.

One thing I always seem to see is multivalued fields being associated with SharePoint.

So perhaps it has it's uses there.

 

[boblarson]

If you can, I would avoid multi-value fields because - while you can access them and such in queries, it becomes way more difficult to do sometimes and you can't use normal update or append queries with them. Use a junction table. That is my suggestion.

 

[HiTechCoach]

I agree that you should avoid using multi-value fields just like you should avoid the evil table level look-up. They may both be helpful for the absolute Access/database design beginner. But the quickly become problematic and a hindrance.


Also see:

The Evils of Lookup Fields in Tables​