Correctly designing table(s)

[hansjhamm]

I have sort of asked this awhile back and was pulled onto other
projects...
Our company utilizes PDA's, modems, laptops, cell phones for the field
employees and managers that we need to record.
Each type of equipment has different data(fields) that will need to be
recorded; example a cell phone will have the phone #, and radio #, but
a PDA would not have this data entered since it does not pertain to
that unit. There are a few equipment items that would only include the
ID, EmpID and EquipmentID as these do not have specific data that
needs to be recorded like a laptop would need to be recorded.

The layout of the table to store the equipment data would be laid out
like this:

ID auto PK
EmpID FK from employees table
EquipmentID FK from equipment type table
(then fields that the user would fill out such as)
ModelNumber (All Equip)
SerialNumber (Select Equip)
IMEI (Phone Only)
SIM (Phone Only)
PhoneNumber (Phone Only)
RadioNumber (Phone Only)
Mfg (All Equip)
ServiceTag (Laptop Only)
WinID (Laptop Only)
OfficeID (Laptop Only)


If I create a table "Equipment" inserting the data as described above
am I going to get into trouble later based on the cell phone/PDA
example?

I am having a problem getting this table set up so that it will be
correct and not lead to issues later.



Thanks,


Hans

 

[Guest]

Hi Hans,

I'm no expert, and I'm not 100% sure I understand all the details of your
question, but one thing they taught us in Access class from the beginning was
to try and simplify your tables and your data. Then create relationships
between them based on common data. This gives you maximum flexibility later
on. You can "unite" the data in queries if needed, and then query those
queries as needed.

 

[tina]

you're thinking spreadsheet. in a relational design, you take data out of
fieldnames (MondelNumber, SerialNumber, etc.), and move it into fields where
it belongs. in addition to the employees table and equipment type table you
mentioned, you need at least two more tables, as

tblAttributeTypes
AttributeID (PK autonumber)
AttributeName
<list here all the attributes you need to track. this is the same idea as
the equipment type table you already have.>

so you have an equipment types table, and an attribute types table. one
piece of equipment may have many attributes and one attribute type may apply
to many pieces of equipment. that's a many-to-many relationship. in Access
you model it by creating a third "linking" (or "join") table, which will be
the -many side of a one-to-many relationship with each of the other two
tables, as

tblEquipmentAttributes
EquipmentID (foreign key from equipment type table)
AttributeID (foreign key from attribute type table)
<use these two fields as a combination PK, or you can add an autonumber
field as a surrogate PK>
AttributeValue (this field will contain the actual model number for a
specific piece of equipment, for instance, or the actual IMEI, or whatever
the attribute type for the specific record is.)

the relationships are
tblEquipmentTypes.EquipmentID 1:n tblEquipmentAttributes.EquipmentID
tblAttributeTypes.AttributeID 1:n tblEquipmentAttributes.AttributeID

suggest you read up on the principles of relational design (normalization).
for more information, see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

hth