NOOB General Design Question - Setting and Changing Default Values

[billybeer]

So I have a database for entering records. Each entry must include in it the person entering the record along with which division, unit and team they are currently assigned to. The tricky part is that people change division, units, and/or teams all the time. I need some general advice at how to go about designing the database so that a person can enter in their id number, name, division, unit, and team once and whenever the open the the database that information will automatically default into the form. Additionally, they must be able to change the information when they change division, unit or team and update the defaults. What I hope to come up with is a way when the person opens the input form they can type in their id number and pull their name and current assignment (division, unit, team) and that information will be pass into the new record when they enter it. They also must be able to update their information when they are reassigned. Help

[ItsMe]

What you can do is create a table for each entity that can stand alone. For instance, create a table for Divisions and another for Units. With this, you can take the Primary Key value from these tables and store the value in another table as a Foreign Key.

I think what is going to happen in your case is that you will not be able to manage all of your entities with simple One to Many relationships. I am going to take a wild guess that a Division can be comprised of many Units and Many Teams. This can be managed with a One to Many relationship. However, it starts getting tricky when you want to query a team's relationship to different Units. Perhaps Teams and Units can be associated in a way that creates a Many to Many relationship. One team can be associated to many units and any one of those units can be associated with many teams.

If a team can be associated with multiple units at a specific point in time, you will need to plan for this.

[Paul H]

This is how I would go about it. I would have a user table set up with their id number, name, division, unit, and team in it and also a beginDate and an EndDate to keep track of the status of each use. The begin date is the date they were assigned to their new team. For their current assignment the end date could be set to a distant future date to ensure it would not expire accidentally, i.e. "1/1/2525". When they receive a new assignment, you would terminate the previous assignment with yesterday's date and start a new record with today's date and current assignment and and ending date of "1/1/2525". in the other table you would just put in the user id and entry date along with the rest of the information. When you query the tables you would check against the user table "where tblData.UserID = tblUser.UserID and tblData.EntryDate between tblUser.BeginDate and tblUser.EndDate. This should always return the Division unit and team at the date the new data was being added. You could do all the in the query by design window. I've done this a number of times, but not lately. I've avoided trying to write this in straight SQL. I hope I've explained this to you accurately. Critiques are welcome.