Design problems

[Wazz]

Hi,
I am wanting to make myself a database but I am struggling with the design of it. I feel I may be making it more complicated than it needs to be, but cant think of a better way to do it!
I want to make a DB that I can use monitor information regarding the comms rooms at my place of work. I want to record info such as Room location, floor number building name etc then info about what switches/routers are in it, how many ports they have and from that how many ports are left. I will require a way to enter new items, so, if a new switch was installed to increase the capacity of a comms room I can reflect that etc.
I was thinking along the lines of having a table for each comms room (approx 70 of them) then a table for each type of switch/router we have (including no. of ports) and adding a router to a comms room and making a total capacity figure... has anyone done this before or think that having 70+ tables in a database is silly? I was only thinking this because I cant have duplicates.
Thanks Guys for any help you can offer!

 

[GingerR]

Hi. Why would you have a separate table for each Comms Room?
Let's take a simple set up, maybe yours is more complicated but if you get this gist of this, maybe it will help. Essentially, you put data into a table for an "entity" one time; once you start making another row for the same "entity", it's time for another table.

Sounds like you have (at least one) Many-to-Many relationship going on here. Each room can have one or many pieces of Equipment; Each piece of Equipment could belong to one or more Comm Rooms.

So you have a table of CommRooms that stores unique info for each (each CommRoom has one row):

CommRoomID CommRoomNo Building Floor
1 A101 205 1
2 B301 202 3

Then your "Equipment" info:

EquipmentID EquipmentNumber EquipmentType Manuf
1 30445x Router HD
2 58861pp Router HD

Then to mate the two, you have a Many-to-Many table (called for example CommRoomEquipement):


CommRoomID EquipmentID
1 1
1 2
2 1

So RoomID 1 has two pieces of Equipment (1 and 2); RoomID2 has one piece of Equipment (1).

So your gig will be more complex, but this is the gist.

Later you can make a Form based on CommRoom table, with a Subform based on "CommRoomEquipment" table. Use the Master/Child linking to hook the two up.

Read up on Tables and Relationships, and Normalization.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Wazz]

Thats exactly the sort of answer I was after.. Thank you!!

 

[Wazz]

Hi again.
Work has had me involved in something else, but I have a bit of free time to look at this now (its an unofficial projet of mine).
I have been asked by a colleague to collect different data, so the above isnt strictly true anymore. I have 1 main form Frm_EquipDetails and three other forms I want to be subforms, Frm_OOS_sub, Frm_Repair_sub, Frm_Comms_Room_Details_Sub
In my main form I have a combo box which contains 3 entries to reflect the sub form names, I want to change the subform based on the combobox selection, but cant, can anyone help with this? I have tried but kleep getting run time error 424, tis driving me mad!

I will then 'try' andincorporate the bits I started off talking about.. one step at a time eh!

thanks everyone for any help at all!!

 

[jedraw]

Wazz
Here are some good links for samples and explanation:

http://r937.com/relational.html

http://www.allenbrowne.com/casu-22.html

http://www.fontstuff.com/siteindex.htm#access

 

[Wazz]

thanks!! I will have a look later today