Jump to content

Similar data, multiple tables...

Owen Mathews

This topic is 6638 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

This topic is 6638 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.