Need help setting up my db

scuzzo84

We have 3 buildings here at work. Each building has X amount of classrooms. Each classroom has X amount of computers. For each classroom, the computers will have the same software through out the computers in that room, but each classroom has different software.

Example: Building "Red" will have classroom "S702" and all the computers in that room will have Windows 2000 and MS Frontpage and so on installed...

And then in Building "Green" will have classroom "T100" and all the computers in that room will have Windows XP and Macromedia Dreamweaver and so on installed...

So I want a website where a teacher can look up a her classroom and see all the software that is installed in her classroom.

So Mrs. Doe will look up her room "S702" from her building "Red" and see

Total computers: 25
Software installed:

software | version |
1. Windows |2000 |

2. MS Frontpage | 2003 |

So this is what I have so far for setting up the database:

Classroomdb
[red] ID(unique, auto increment) , room(varchar), computers(int)
[green] ID(unique, auto increment) , room(varchar), computers(int)
[black] ID(unique, auto increment) , room(varchar), computers(int)
[software] ID(unique), name(varchar), version (varchar)
[classrooms] room id? , software id? confused........

I dont know how to do the classroom part, I was thinking a join but since there are 3 buildings and having to join the ID with 3 IDs wont work.

scuzzo84

this seems the way to go

tblClassroom

Building
ClassroomNum
*SoftwareID
NumOfComputers

tblSoftware

*SoftwareID
SoftwareName
Version

bpat1434

I'd separate even further so that the buildings are referenced by number (so if I want to change the name I change it once). Other than it looks good.

Roger_Ramjet

bpat1434 wrote:
I'd separate even further so that the buildings are referenced by number (so if I want to change the name I change it once). Other than it looks good.

Yes. Having just 1 table with the classroom data rather than 3 will make it easier to do things like find all the rooms with specific software regardless of building.

building table
b_id
building

classroom table
c_id
b_id
room
function

software table
s_id
publisher
name
version
date_in

computer_software
x_id
c_id
s_id
date_in

I would also have a computer table for asset management/service tracking etc

computer table
p_id
c_id
asset_tag

Now, you can add buildings, add classrooms, or even move to another site without having to change your db structure. You can change the usage of a classroom, move computers around, and install new versions or complete packages at will.

This schema would also support easy extention to include time-tabling by class size, software needs, or prefered location. And as I indicated, it can be extended to cover complete asset management of the hardware, it's service history, and project planning for new software rollouts.