Table / relationship Questions

[eolson30]

Hey guys. I was tasked to create a database that would store the data for our SWAT team. Im having a little dificulty with it. here is the details on the Data we need to store.



1. Supervisors
2. Officers.
3. Tactical Bag Inventory for each Supervisor and Officer.
4. Certifications for each Supervisor and Officer.
5. Other trainings for Supervisors and Officers

Each supervisor and Officer is assigned tactical bag that has equipment in it.
the Supervisor Bags are labeld with their Last Name
The Officer Bags are labeled with numbers #1 thru #30
The reason the Supervisor bags are LAst name is because we keep supervisors on ther team as long as they are employed, where Officers acan transfer and they will not keep their bag.
each bag has the same equipment in it as any other officer or supervisor. The only difference would be the sizes of specific things.
Each Supervisor and Officer is trained in certain things and will be offered certain trainings.

I dont know if my tables are set up correctly and how the rerlationships need to be put together.

The tables i have are:
Supervisors
Officers
Items
Trainings
Certifications

When a new officer or supervisor is added i would have to add their personal info, assign them a bag and enter the equipement for the bag. then enter their certifications and any other trainings they receive throught their employment.
If anyone can help me that would be great. I have attached the Database with the tables SWAT Info.zipSWAT Info.zip

[RuralGuy]

I would think "Supervisor" would simply be a field in the Officers table.

[eolson30]

I thought that too, But i have to keep Supervisors and Officers seperate. And i cant have 2 seperate Databases.

[RuralGuy]

I thought that too, But i have to keep Supervisors and Officers seperate. And i cant have 2 seperate Databases.

Is this *orders* from headquarters?

[eolson30]

When you mean "orders" im guessing you mean instructions from my supervisor. Then yes. We wanted to have one database for all the employees. but needed to seperate the supervisors from the Officers. If there is a way to do that and put the Supervisors and officers in one table than that will be ok.

[RuralGuy]

It will all be in one db. Again, I would say that the Supervisors and Officers can be in one table and then Supervisor or Officer, whichever you choose, would simply be a field in that table. Then the same form is used to add/edit all individuals.

[eolson30]

Ok that makes sense. Actually theres a rank field, that i could keep that states either a supervisor or officer. Cool, thank you. What about the relationships between the tables. i always get confused with how to link each table and the "enforce Referential Integrity". Along with one-to-one, one-to-many, and the many-to-many. how do i do that

[RuralGuy]

Start by adding an Autonumber field to each table and set it as the Primary Key (PK) field. Then the other three tables will need a Foreign Key (FK) field that contains the PK from the Supervisor/Officer table and all of your relationships will be 1:M which Access will detect and set for you in the Relationships section.

[eolson30]

The tables have PK what do you mean FK can you take a look at the zip file I included and see if it's right

[RuralGuy]

The three tables other than Officers/Supervisors are Lookup tables. You will need a table of the Items that each Officers/Supervisors has and it will need at the least a field for the Officers/Supervisors involved and another field for the Item included. These two fields should only have the PK of the table involved. PK of the Officers/Supervisors table and PK of the Item included. You could also have a date field of when issued and any other data needed for each item. This Item table will have a 1:M relationship with both the Officers/Supervisors table as well as the Items table. Many Officers/Supervisors can have each item and each Item can have many owners.

[eolson30]

I created another table called "Transactions" which has 3 fields: EmpID, ItemID, DateIssued
here's how I have them linked

[Employee] EmpID 1-to-many [Transactions] EmpID
[Items] ItemID 1-to-many [Transactions] ItemID

I hope this makes sense is this what you meant

[eolson30]

Here is a screenshot

[essaytee]

Just my two cents worth. You may need to reconsider your Certifications table. What will you do if a new certification is introduced where it doesn't fit into your pre-defined fields? Seems that a 'date of certification/completion' should be included somewhere. I'd say, handle this aspect as you have as per your 'transactions' table.

I'm assuming though that certifications somehow hooks off the training table. In your Training table the field 'Training' can be a FK to the 'Certifications' table. Your Certifications table fields should be reduced to (CertID, CertificationType (records being, handgun, shotgun etc.) any other direct pertinent data regarding Certification type. Remove the link from Employee to Certifications.

As I said, just my two cents worth.

Steve.

[eolson30]

Thanks essaytee, i understand. your right the certification table should come off of the training table. that does make more sense. as for the certifications these are the only certs that you are able to get certified in any others are trainings. The problem i was having was the relationships between each table. i never understood and could never get any of this to work. Is this what you meant essaytee

[essaytee]

Thanks essaytee, i understand. your right the certification table should come off of the training table. that does make more sense. as for the certifications these are the only certs that you are able to get certified in any others are trainings. The problem i was having was the relationships between each table. i never understood and could never get any of this to work. Is this what you meant essaytee

Hi eolson,

I'll refer only to the Certification issue regarding the design at the moment. A linking table between 'Training' and 'Certifications' is recommended. The linking table fields being;
TrainID, CertID (consider 'date of' field if this not the same as 'DateCompleted' from the Training table). The fields, TrainID & CertID, combined is the PK. Each of these fields are the FK's to the Training and Certification tables (hence, I refer to this table as a linking table).

From your existing Certifications table remove the fields EmpID and DateOfCertification (not required). This table is your Certification lookup table.

I'm assuming the officers attend a training session, which may last a day, or two or three or longer. Also assuming that during the one training session officers can receive multiple certifications.

I'm in the same industry here in Australia and so what also comes to mind are expiry dates of certifications. If this is a factor, it's merely another field in the linking table.

As mentioned, I've only addressed the certification issue. If I get time I'll try and upload a Relationship graphic to help better explain the above.

Steve.