Similar data, multiple tables...

[Owen Mathews]

So I'm building a database for tracking computers. They can be assigned to a person, a location (e.g. a computing lab or classroom), or a group (e.g. the technology department). I have tables for each of these kinds of things, plus a table that stores the associations by keying on unique IDs (I use textual serial numbers like person0, location1, and group2).

The only problem I'm running into is when I design UI screens, because then it's often necessary to have three basically identical layouts, one for each thing. It just doesn't seem very elegant: Create new repair incident (search by Person, Location, or Group); Assign computer (to Person, Location or Group); etc. Wouldn't it be nice to be able to just use one layout and look up the correct thing based on the name (assuming of course that names of people, groups and locations don't overlap)?

My instinctive reaction, not being a FileMaker person, would be to create an intermediary table that stores an ID-name pair, with the name field being calculated based on the ID. Of course this creates a consistency problem because you have to be sure that this aggregating table contains all the IDs from each of the three other tables.

So my question is: if this is the right way to do this, how do I automatically trigger a new record in this aggregate table every time a new person, group or location record is committed, and also delete when one is deleted? And if there's a better way that doesn't involve scary information duplication, what is it?

The only other solution I can think of is to use just one table to store all people, groups and locations. Then it becomes a little weird because some fields (such as last name) will only pertain to certain records, based on some field that acts as a selector. For some reason this just seems too kludgy to me.

[Ender]

Are you also planning on relating Person to Group (to know who is in each group) and Person to Location (to know where each Person is located)? Or is this not necessary?

If you don't need those things to be related, then I'd probably put the entities together in one table. This will give you common fields of User_Group_Loc ID and Type. Then you would have separate layouts for certain Type specific functions, like data-entry and some reports.

Though this is close to the same thing as you currently have, this design does make some things easier. You can easily run a single report to show which User/Group/Location is assigned to a piece of equipment. You can reuse some common functions a little easier.

I've attached a diagram.