Relationship problem (1 Viewer)

[Jerry Woodstock]

I have created a database for my work but i am still not happy with how the data is being stored.This database was intended to keep information on users there position etc as well as track equipment with the user. basically its three tables that are causing me the greif; equipment,Personal and Location. The problem im haveing here is this i current have Location associated with Personal and then a relationship drawn form personal to equipment. the problem here is when people leave(which is quite often) the equipment loses its location.but if i arrange it so the relationship is as follows Location Equipement Personal . A Problem arises in the aspect when a user has piece equiment of equipment at his home instead of the office then the owner of the buisnees wants to beable to have equipment associated with user. which for reason makes sense because u dont want to have a location such as Bob'sH House etc in the location table.

If anyone could help it would be much appreciated

Thanks
Jerry

 

[The_Doc_Man]

The problem is because you didn't model it quite right.

Both equipment AND people can have locations that are independent of each other.

Person
- company employee num (PK)
- data about the person
- location = Location's location number (FK)

Location
- location number (PK, autonumber)
- address
- room
- other appropriate info

Equipment
- company inventory number (PK)
- assigned to = Person's company empl num or 0 if in a pool (FK)
- location = Location's location number (FK)
- other appropriate info

Relation 1: Equipment's location code to Location's location code, with one Location to many Equipment entries.

Relation 2: Person's location code to Location's location code, with many Persons to one Location.

Relation 3: Person's employee code to Equipment's assigned-to code, with many Equipment items to one Person

 

[Jerry Woodstock]

Thanks !

one Question though i thought there was some rule saying that you couldnt have tables in a loop like that ? just curious as im not much of a database programmer


thanks again
Jerry