Table Relationships in Access 2007

[stinkynathan]

First, I'm not sure if this should be in this forum or in programming; mods, please feel free to move it if it's in the wrong place.

I took a DB class about four years ago when I was a CS major. The basics have stuck with me, but I'm still having a problem.

I'm working on an inventory and personnel DB for a high/middle school band department. The tables I have pertaining to my problem are:

student personnel (info, instrument#)
instrument inventory (info, instrument#)
colleagues (info, instrument1#, instrument2#, instrument3#, ..., instrument20#)

The relationship between the student's instrument number and the instrument number in the inventory works well. My query of the student table will return all info about which instrument has been checked out to each student.

Because of the school district's shortage of instruments, teachers from other schools can check out instruments from my school. I'd like to have the same type of relationship between the colleague and instrument inventory tables as I do between the student and instrument inventory tables. Catch what I'm saying? I'm trying to future-proof this and make it so colleagues have the potential to check 20 instruments out from my inventory, however unlikely that may be

At this point, if I use the student query (modified, of course) to find out which instruments each colleague has checked out, nothing is returned. I'm not sure if this is a query problem or a problem with how I have the relationship set up within the database.

Any help is definitely appreciated.

 

[KLin]

Can't really help without query statements and more detailed information on the table setup. Please provide details.

 

[KB]

Your colleagues table will make growth and querying a pain. If a colleague has only 1 instrument you will have 19 empty columns, plus you will need to join your query on 20 columns.

This is closer to proper DB design:

I never use # in column names. It doesn't work across all DBMS.
instrument should have a database id column (auto increment) and an inventory asset tag number. Asset Tags change, the database id never should.

instrument (instrumentid, instrumentnumber, instrumenttype, datepurchased etc.)

colleagues (colleagueid, name, phone, school, etc.)
colleagueinstrument (colleageinstrumentid, colleagueFK, instrumentFK, dateborrowed, datereturned)

with colleagues being one-to-many to colleagueinstrument on colleageid = colleageFK

I would also do the same with students:

student (studentid, name, grade etc)
studentinstrument (studentinstrumentid, studentFK, instrumentFK, dateborrowed, datereturned)

 

[stinkynathan]

Originally posted by: KB
Your colleagues table will make growth and querying a pain. If a colleague has only 1 instrument you will have 19 empty columns, plus you will need to join your query on 20 columns.

This is closer to proper DB design:

I never use # in column names. It doesn't work across all DBMS.
instrument should have a database id column (auto increment) and an inventory asset tag number. Asset Tags change, the database id never should.

instrument (instrumentid, instrumentnumber, instrumenttype, datepurchased etc.)

colleagues (colleagueid, name, phone, school, etc.)
colleagueinstrument (colleageinstrumentid, colleagueFK, instrumentFK, dateborrowed, datereturned)

with colleagues being one-to-many to colleagueinstrument on colleageid = colleageFK

I would also do the same with students:

student (studentid, name, grade etc)
studentinstrument (studentinstrumentid, studentFK, instrumentFK, dateborrowed, datereturned)

OK, I think I understand what you're saying. Please correct me if I'm wrong.

(These are already set up, need some modification with your ideas)
A table for student information.
A table for colleague information.
Individual tables for lockers, padlocks, and instruments.

Then:
Another table that essentially says, "Joe Student was issued instrument1 on $date," and "Jane Student was issued instrument2 on $date," etc. Related to studentTable by unique student ID and related to instrumentInventory by unique instrument ID.
A table that says, "Jim Teacher borrowed instrument3 on $date," and "Sharon Teacher borrowed instrument4 on $date," etc. This would be related to colleagueTable and instrumentInventory in the same way the student one is.
Same concepts hold true for issuing locks and lockers.

Essentially, student, colleague, and the inventory information should never change unless people or stuff are added. The only thing modified when equipment changes hands is the table that says who has what.

...and I wasn't using "#" in any of the column names. I was just abbreviating things for the sake of the post.

 

[KB]

I think you got it. Each student would have one-to-many lockers and padlocks; however if students share lockers then this would be many-to-many and you would need an additional table for this relationship.