Table / relationship Questions

[eolson30]

Hi Steve thanks for the explanation I think I did it correctly

As for trainings the list of certifications are what our dept specializes in not all staff will have these. Staff can also sign up or go to many other trainings offered to them

In our dept once you are certified you stay certified until you quit or retire from service

Thanks for the help I look forward to the relationship graphic when you have time and also any other help that you can offer

[June7]

I am confused by the Certifications table. One CertID for multiple categories? Are these Yes/No fields?

[eolson30]

Hey june7 yeah the certification table has yes/no Check box's for the certifications

[June7]

So a single CertID can be associated with more than one category? Same CertID can be associated with multiple training records and therefore multiple employees can be associated with the same CertID?

[essaytee]

In my opinion the certifications should be in a lookup table. The lookup table will have 10 records. If in time there are no additions to the certifications, so be it, no harm done.

Anyway, here's a graphic, not completely as I described in my previous post. It's a simple model. Table names are purely for explanatory purposes (naming conventions is another topic).




Hope this helps.

Steve.

[eolson30]

Hi Steve this model does make sense. i did have a few questions. And im sorry, but im very new to databases. For the Inventory part of this how would the linking tables be. Each member has an assigned bag and in those bags are equipment. How would i link the tables for that. im so confused with all the tables and linking. Then how would i add data to the tables.

[June7]

By 'assigned bag' do you mean these bags have an identifier number and are accountable? Each employee has only one bag? Have field in Employees for BagNumber. Is the equipment the same in all bags? If you need to list the equipment within the bag then need another related table.

Use forms for data entry/edit. Might want some form/subform arrangements.

[eolson30]

Yeah each member has an assigned number but our supervisors bags are assigned with their last name.
Each employee has one bag and everyone's bag have the same items just different sizes for example tactical pants sizes, helmet size and other things but everyone has the same items

What do you mean form/subform arrangements

How would I link the table PK/FK

[June7]

Each member is assigned an ID number but bags do not have an ID number, like a serial number? Consider a related table that has equipment assigned records. The member ID would be foreign key in this table. There would be many records for each member.

http://office.microsoft.com/en-us/ac...010098674.aspx

[eolson30]

No the bags don't have a serial number each bag is numbered 1-26 then the supervisors bags are labeled by their last name sorry I'm a visual person reading it and understanding it is harder for me

[June7]

Do you care that Joe has bag 23 or do you just care about what equipment Joe has? If the bags have identical equipment, exactly what needs to be tracked and accounted for? Do items have serial numbers? Do you want to document each person's sizing info?

Options:

1. a table that lists equipment in each bag and then the bag number/name is entered to field in Employees table

2. a table that lists equipment assigned to employee, this table has EmployeeID as foreign key.

Take your pick. In either case, the table structure options:

1. normalized - a record for each item in the bag

2. non-normalized - a field for each item with appropriate descriptive info (size or quantity or serial number) in the fields.

[azhar2006]

In addition to what my friend (June7)
Work schedule arranged for the military and make it includes (officers + supervisors)
And associated external switch for each employee

..................................
Table ranks
Employee table
Table Departments
table that lists equipment

[eolson30]

Hey June7. Yeah i do need to know what bag each employee has and also what items are in their bag. the items are all the same, but the sizes and quantity would be different for each employee. The items dont have a serial number. We need to make sure that everyone has all of their equipment. We have training every month. after each training we do an inventory to make sure noone lost or has taken any of their equipment. Also by keeping an inventory we can keep track of the total amount of items we have, and what is left that hasn't been inventoried out to an employees bag. if that makes sense.


azhar2006 - what do you mean a military schedule.and associated external switch for each employee.

[June7]

So which suggested options do you like?

[eolson30]

Sorry for the late reply i was out of town. I like the 2nd option. because even if each employee has the same equipment, the sizes will all be different.

2. a table that lists equipment assigned to employee, this table has EmployeeID as foreign key.