Table relationship question

[Lothar]

I am creating a database that will be used to track employees, computer inventory and the positions allocated to my division. I currently have three tables created as follows:

Position#TBL:

• Position# (primary key)
• ReportsTo
• FormalTitle
• WorkingTitle
• classification
• FundingSource
• EmployeeNumber

EmployeeTBL:

• Employee Number (primary key)
• LastName
• FirstName
• HireDate
• Email
• Position#
• PC#

ComputerInventoryTBL:

• PC# (primary key)
• Make
• Series
• ModelNumber
• ServiceTag
• OperatingSystem
• EmployeeNumber

The following business rules apply:

• We have a defined number of positions in our division, each has been assigned a unique position number.
  
  • Each position number can have only one employee assigned to it at a time but over time, multiple employees will be assigned. We are not especially concerned about tracking what employees have historically been assigned to that position number.
  • Position numbers may not have any employee assigned if the position is vacant.
  
  
• Each employee has a unique employee ID that stays with that employee forever.
• An employee can only be assigned to one position number at a time but over time can be assigned to multiple position numbers. We are not especially concerned about tracking what position numbers an employee has been assigned.
• Each employee can only be assigned to one PC at a time but over time can have multiple PCs assigned to them. We are not especially concerned about tracking all of the PCs an employee has been assigned to historically.
• Each PC has a unique PC#.
• Each PC can be assigned to only one employee at a time but over time, a PC can be assigned to multiple employees. We are not concerned about tracking all the employees a PC has been assigned to historically.
• PCs can be retired so I think I need a checkbox field to identify if a specific PC is active or not.
• Employees come and go so I think I also need a checkbox field to identify if an employee is active or not.

My main questions right now are about relationships between the tables.

1. It seems fairly obvious to me that the EmployeeNumber field in the EmployeeTBL should be linked to the EmployeeNumber fields in both the Position#TBL and the ComputerInventoryTBL. Should that be a 1-to-1 or 1-to-Many relationship?
2. It also seems fairly obvious that the Postion# field in the Position#TBL should be linked to the Position# field in the EmployeeTBL and the PC# field in the ComputerInventoryTBL should be linked to the PC# field in the EmployeeTBL. Same question as above, should these be 1-to-1 or 1-to-many relationships?
3. If I create these relationship, shouldn't the data that I have in the EmployeeTBL automatically fill the corresponding fields in the other two tables? Ultimately I plan to create an entry form that will be used to add a new employee and assign them a PC and position number or it will be used to change an existing employee.

I've tried to create databases in the past but I always seem to get hung up on the relationship piece. Any help would be greatly appreciated!

[ranman256]

Never assign 1 computer to a person. Remove the PC#. (even tho you say it now, it could come back on you)
Every Equip goes to the INV tbl.

1 employee has many equip.
I wouldnt do much on the reationship links. I RARELY use them and it prevents headaches later and it all works anyway.

Use form -subForms to view the Emps equipment.
I would also make some queries so you can MOVE equip from 1 person to another. (I used a form to pick current owner, then new owner and move in a single click_)

[Bob Fitz]

I have only had the most cursory look at this thread, but would advise the OP not to use the "#" character in the name of any object, like field or table names.

[orange]

Only use alphanumeric characters in your field and object names --no special chars (#,?...etc).
I'd be cautious with the "we're not concerned with XXXX" -- design for the general case. You'll be surprised at the "oh could you adjust it to do YYY and WWW?" Better to design in some "potential aspects" now-- it will reduce the redesign and test effort later.

You started with:

Positions, Employees and Computers

I see ServiceTag which indicates some repair, but I don't see a Service or Maintenance table mentioned.
Operating System can change with time. Depending on WHAT you do with OPeratingSystem ( and perhaps SM level) you may want a table to record PC Operating System history.
Similarly PositionNumbers can change with a change in management and/or reorganization (I've been through many).

Unlike ranman256, I always create a data model and vet it with the business process descriptions and test data. From my experience you can solve a lot of design issues by involving users and management in the design process by playing "stump the model".

Stump the model was the term we used to collectively ask questions of the model (of the proposed and evolving database). Using the business rules that had been collected and some representative test data, participants would ask questions. The modeller or design team would lead the participants through the answers by working the question/data through the model. At anytime, if the question could not be addressed by the model, a discussion would occur to reconcile the issue. Was the test data incorrect? Was one or more business rules incorrect? This had to be reconciled before proceeding.

Note: The whole idea of stump the model was to seriously "attack" the model, NOT the modeller or designer. The model was a representation of the facts that had been gathered through specifications, interviews, etc. Often, different users saw different things and often different views of the business (or business process). The "stump the model" sessions, often taught/showed the various users parts of the system/business with which they were not familiar.

Tools such as ERWin (now CA), Designmanager, VISIO and a host of others do exist. You can also build a demonstration prototype and use the Access relationship window as your data model. Agreed, it isn't the best, but certainly workable for a small database.

A demo prototype from my perspective (also called stub processing) just displays messages of WHAT should happen when you click this button, or select an item from a combo etc. Just enough to demo the functionality that will be developed. Better to get agreement on this before doing a bunch of coding only to find out "that isn't what we meant".

Good luck with your project.

[June7]

Underscore is the only exception to not using special characters/punctuation in naming convention.

The only data that can and should 'automatically' fill is the PK as FK in related tables. This is best accomplished by form/subform arrangements for data entry/edit.