Multiple fields for same item

[TaiChi56]

I really was not sure where to ask this question. I hope I am in the right spot. I am inventoring technology equipment at a very large school. I am building a database so we can track all of our equipment. Here is the problem that I have. I have many rooms that have many computers. All of the same kind and they are indentified by a Service Tag# and an Asset tag#. How would I implement that in a database. Right now I am doing, Computer#1, Computer#2, Computer#3 etc... up to 30 computers per room. Is there a better way to put it in a table? Maybe a value list? Please advise. Thank you.

[mr_vodka]

A basic setup for this would be to put the inventory in a related table. Room --< Assignment >- Equipment.

[TaiChi56]

Sounds good. I will give that a try.

[comment]

I have many rooms that have many computers.

 

But each computer has only one room, right? In such case, you only need two tables: Rooms (parent) and Equipment (child).

 

However, if you wish to keep track of each computer's movements from room to room, you will need to go with the three tables arrangement suggested by John. In any case, multiple numbered fields are not the way to go. A portal to Equipment, placed on a layout of Rooms, will give you the multiplicity you require.

[TaiChi56]

I am having a hard time figuring out how to develop my database. I know that it is a problem with me understanding relations between tables. I have read different articles but still have a problem. Let me show you my relationship first.

 

Table 1                     Table2

Room                       RecordID

Phone                      ServTag

StaffMember            AssetTag

RecordID                 Printer

 

The recordID is a unique serial number to each recordit is related in my relationship graph. My problem is when a room has more than one computer. Some rooms have one computer, while others have up to 30 computers. I have a field called ServTag (Service Tag #) this identifies that computer. But if I have 30 computers as soon as I put in a service tag for one compuer the rest of the 29 fields show the same number. How do I make it unique? I know I am missing something basic.

[Melinda O'Brien]

I feel  your pain. Relationships can be tricky, especially when you are first figuring them out. At some point, things will click and you'll wonder why it was ever so difficult. 

 

Since I've read another post for you, I am working on the assumption that Table 1 refers to ROOMS and Table 2 refers to COMPUTERS or possibly EQUIPMENT. I am also assuming that no two rooms will have the same Number/Name. For this relationship, I would add a field to Table 2 called Room. Then create a relationship based on Room where Table 1::Room = Table 2::Room.

 

You would then need to create a portal on a Table 1 layout based on that relationship. You could then create new records in Table 2 through the portal and FM would automatically put the Room number/name from Table 1 in the Room field on Table 2. Or you could create a new record on a Table 2 layout and assign the Room number there (you would just need to make sure it matched the room number from Table 1) and it would then show up in your portal on the Table 1 layout.

 

With this scenario, only the computers on Table 2 that matched the room number on Table 1 would show up in your portal.

 

I hope that answers your question.

[comment]

This is a duplicate thread:

http://fmforums.com/forum/topic/90385-multiple-fields-for-same-item/

[comment]

I am also assuming that no two rooms will have the same Number/Name.

 

That wouldn't be so bad; however, you are also assuming that a room's name/number can never change - and that's not good practice. Instead, you should define your relationship as:

 

Rooms::RoomID = Equipment::RoomID

 

where RoomID is an auto-entered serial number field in the Rooms table, and  a simple number field in the Equipment table. That should take some of the pain off.

[Lee Smith]

This is a duplicate thread:

http://fmforums.com/forum/topic/90385-multiple-fields-for-same-item/

I have merged the two topics.

 

@TaiChi56

 

Please do not multiple post your questions.

[Melinda O'Brien]

That wouldn't be so bad; however, you are also assuming that a room's name/number can never change - and that's not good practice. Instead, you should define your relationship as:

 

Rooms::RoomID = Equipment::RoomID

 

where RoomID is an auto-entered serial number field in the Rooms table, and  a simple number field in the Equipment table. That should take some of the pain off.

Yes, that makes even more sense.

[TaiChi56]

I thnk I am getting it. I will repost if I do not. Thank you for everyones help.