TC said:
I am trying to build a database for equipment maintenance for a construction
company and I need help with almost everything. I am using Microsoft Access
2007 and I am confused.
I have some tables built.. I have broke out heavy equipment versus light
duty vehicles, rental equipment, parts used for maintenance. I think I
should have a table with employees and what light duty vehicle they are
assigned to.
I can't seem to figure out how to move forward. I posted basically the same
question October 23rd, but can't find the question.
Step away from that computer!
Now get a large sheet of paper a pencil and an eraser. On the paper draw
a circle for each type of thing that your database needs to know about.
Now add lines that connect different types of things. For instance one
employee may assigned to a vehicle during their shift. You need a line
from employee to shift and one from vehicle to shift. Because these are
1:many relationships draw a crows-foot symbol at the end the line at the
many end, like -----<.
If you find that you have a many:many relationship then break the line
in the middle and add an empty circle that has two 1:many relationships.
The chances are that you will find out more about these empty entities
and will end up giving them a name.
When you have everything drawn on the diagram go away and have a beer or
two, you will have earned it.
Come back to the diagram a day or two later and decide whether it still
makes sense to you. If it does then you have completed a very important
document, an Entity Relationship Diagram. Specifically this is the
Logical Data Structure ERD. When you get to convert these entities into
tables you can create a Physical Data Structure ERD.
Next take your yellow pad and write down what your system needs to know
about each of those circles, the entities. Each of those entities is
likely to become a table in your database and everything you have on
your yellow pad is likely to become a field. For each entity you should
look for a field or combination of fields that uniquely identifies each
instance of the entity. These fields are "candidate keys" in your
database tables.
For instance the People table will likely hold a person's first-name and
last-name and possibly a middle-name. Finding candidate keys for a
People table is difficult, but someone may have done the hard work and
given each employee a payroll number or something similar.
Only when you have all of the entities linked by 1:many relationships
should you start creating tables. One per entity. An experienced
database analyst or someone who knows the data very well may be able to
safely merge two entities into one table. Bear in mind that there is
always a price to pay for moving away from the true logical data
structure. If you don't know what the price will be then don't do it.
