To Query or Not To Query

[Zaeed]

For my database, various role names can change every now and then. In order to combat this, i'm thinking of creating a query for certain positions.

What I mean is:
Production Manager is a pivitol role, since it gets referenced to for approvals and placed in emails etc. If this title changed, I would have to re-write references to that position in code..
I was thinking about creating a query, qry_ProductionManager for example, that held just this one particular person, the person currently assigned to that role.

By doing this, my code can simply reference the query. In the event of the role name changing, or the person in that role, the contents of the query is all that needs to be modified, rather than have to dive into the code. This way a non VB programmer can make updates.

Before I went ahead with this though, I thought it might be wise to check if this is the best method to acheive my goal..

Cheers for your help

 

[Galaxiom]

I'm glad you asked because you would otherwise be working around a structural weakness in the design of the database. You are heading in the right direction but not far enough.

Never store the title of a position other than in one table with a PK. In this case you would have a Position table with PositionID, PositionName and the key from the Personel table to indicate who is the current holder of the title. Likewise the name of the person is store in one place only. All other references to them is by their ID number (the PK of the personel table).

Everywhere the IDs are stored, not the text of the title nor the employees name. To show the title of the position it is is looked up from the table using the ID. To find the name of the person holding the position their key is looked up in the Personel table.

Change the title or holder in one place and the title and holder displayed in every form and report across the entire database uses that change instantly.

 

[Zaeed]

Hmmm.. Bare with me here..

There are certain positions that have multiple people (i.e. there are multiple 'Safety Advisors') How would I handle this..

I'm failing to see how having a seperate table to hold the positions helps.. Currently my User table has the following fields:
ID, Name, Email, Position, Department

Can you explain the reasoning for your method a bit more please?

 

[Galaxiom]

You said:

If this title changed, I would have to re-write references to that position in code

I assume you mean the title might change from "Production Manager" to "Production Coordinator" for example. You would have to rewrite your code unless you did it something like I suggested.

Have a Position table with PositionID and PositionName.
Your user table would store the PositionID not the text of the name.
Likewise the Department.

Refering to the position in the user table makes sense rather than referring to the user from the position especially where multiple users have the same position. But refer to a key not a word.

 

[Zaeed]

Doesn't that just create an extra step of referencing though?


hmmm, ok.. I've found that I mainly ignore the position information for 90% of the users..

its only the 10% that count..

i have the following Positions that are important...

Safety Partner - Group
Safety Advisor - Individual
Quality Partner - Group
Quality Manager - Individual
EHS manager - Individual
Op Manager - Individual
HR Manager - Individual
HR Advisor - Group
Environmentl Specialist - Individual

both the members, and the position titles can change in the future..

How would you suggest I handle this.. I'm tearing my hair out trying to think of a 'elegent' way of doing it..

 

[Zaeed]

Wait I think it just clicked..

User table: ID, Name, PosID, DepartmentID

Position table: PosID, Position

Safety Partner Query: Select * From Users Where PosID = 7

That work?

 

[Galaxiom]

Doesn't that just create an extra step of referencing though?

In a query:
WHERE Position = "Safety Partner"
will take many, many times longer than:
WHERE Position = 7

An AutoNumber is stored as four byte "words". Comparing these numbers is done by subtracting one word from the other and getting zero or not.

The text in "Safety Advisor" is fourteen bytes. Comparing strings is done byte by byte (character by character) with tests against both upper and lower case then ORing and ANDing the results.

Any yes you are on the right track now.

On a form you might look for a "Safety Partner" by dropping down a combo. However the bound column in the combo will return 7 which is what will go into the query.

 

[Zaeed]

Cool..

Thanks for your help