table construction

[Marcellllo]

Hi guys !

I am new in using Ms Access and I have to design a database about external empolyers. Right now, I have something like that but I am not sure if it is correct because it does not look like most databasesb I have seen on forums or on the internet

In db i have 3 tbls

tblContractors
eTime User ID (PK)
PM Name ( name of project manager) (PK)
First Name
Last Name
Location
Years of experience
Area of experience
General comment

tblSkills
ID (PK)
PM Name
Skill 1 ( main skill)
Skill 2 ( sub skill)
Skill 3 ( sub-sub skill)
Skill 4 ( detailed )
Skill level

tblPriceList
eTime User ID ( PK)
Vendor
Price
Original Start Date
eTime Role

I also provide screen to show relationships between tables and I would like to ask for advice, if construction of tables is correct or if it should be built in another way according to normalization rules.


Thank you very much

 

[Pat Hartman]

Welcome aboard
Your schema does not look correct.
1. If your table contains an autonumber, it should be designated as the primary key (there are bugs if you don't do this). If you have other columns which must be unique either by themselves or in combination with other columns, add unique indexes to enforce those business rules.
2. Why is the PM name part of the primary key for tblContractors? The eTimeID should be unique and so that would be sufficient.
3. If you want to relate a contractor to a PM, use the ID field rather than a name field but I don't see a table that defines PM
4. I don't understand tblPriceList or tblSkills. They both have keys and columns that don't jibe with my understanding of what tables with those names should contain as attributed.
5. tblSkills looks like it should actually be four tables - one for each level in the skill hierarchy.

And finally - column names should NOT contain embedded spaces nor should they contain special characters.

 

[Marcellllo]

Hi Pat, thanks for you respond )

Well eTime User ID is combination of User name with automatic number but it does not makes each record unique becase one person ( with one eTime User ID) might worked with different project managers ( PM Name ).

The second issue is that that each person may have one or more skills in some sphere.

For example 10DITSEP worked with 2 project managers so we can find him twice in tbl contractor and he is programmer with knowledge of VBA,C++,Oracle and SQL so we can find his eTime User ID 4 times in tblSkill.

TblSkills is connection of mentioned skills to specific worker and tblPriceList connects current company, where is worker currently assigned ( his salary, his eTime role, his starting date )

I am not sure if it should be built like this so thank you very much for your advices and opportunity to consult my steps

 

[Pat Hartman]

A person should NOT appear multiple times in the contractor table. He should appear only once. You need to remove the column(s) that are causing the duplication. That is part of the normalization process. So, PM becomes a separate table. You should also have a separate table to define PMs unless they also have eTimeIDs, in which case, you would reference a PM with his eTimeID and the table would reference itself.

Are skills really related to the combination of eTimeID and PM or are they related to the eTimeID? You never said what the app is supposed to track so I can't tell. If you are tracking skills a person has, you need one schema, if your are tracking skills a person used while working for a specific PM, you something different.

 

[Marcellllo]

Hi Pat, Thank You very much for Your advice

That multiple issue stuff is probably because of bad organization of data before .... so I need to make each eTime User ID in Contractor table unique ?

But how should I solve the problem with situation when one worker ( eTime User ID ) worked with more Project Managers ? Can I connect this two entities in some separate table somehow ?


Skills are related to eTimeID .. person with same skills could work with more than one PM but skills are related to worker, not to PM.

This database should be a a storage of new and historical external workers that ever worked for the company. This is probably reason why there are some duplicit values in eTimeID`s and skills.

Do you have some ideas how could I handle it ?

 

[way2bord]

holds employee info.
tblContractors
eTime User ID (PK)
First Name
Last Name
Location
Years of experience
Area of experience
General comment

employee-skill join: links many employee to many skills! (employee can have multiple skills)
tblSkills
eTime User ID (PK)
SkillsID (PK)
Skill levelSubLvl

work: employee can have multiple work sessions (or work history) Each work session can have a single PM - this can be a different PM or the same.
tblPriceList
PriceListID (ID)
eTime User ID (FK)
PMID (FK)
Vendor
Price
Start Date
End Date
eTime Role

skills list: a list of all possible skills
tblSkill
SkillsID (PK)SkillName

PM list: a list of all possible project managers
tblPM
PMID (PK)
PM Name


Made a rough normalization structure for you, while attempting to leave your nomenclature, table purpose, and structure mostly intact. Notes are in italics above each table. It's not perfect, but it's a fairly simple structure to work with.

There are quite a lot of prebuilt employee databases which will offer a solid structure and easy modification if you run a quick search online.

 

[Marcellllo]

way2bord thank you very much for your table design. May I ask what if one worker worked during his life in two different areas ? I believe it would cause duplicity in first table.
PMID, SkillsID are automatic numbers or the same as eTime User ID which is combination of ID and eTime User ?

Thank You very much, It looks very clever )

Can you advice me some of websites where can I find that prebuilt databases ? I only ask in case You have some personal expereience with one and may give me some referencec about that website .

 

[jdraw]

PMFJI,

As Pat said in post#4 re duplication, if worker location could be an issue, or if you could have the same worker working on different jobs at different times (and the same worker could live in a different location), then create a new table for Location
tblLocation
LocationId
LocationName

And then a junction table to handle this Employee at this Location with

tblEmployeeLocation

EmployeeID 'compound PK
LocationId
'you'd probably want a date related field here as well

There are a number of free data models, some of which may be helpful, at
http://www.databaseanswers.org/data_models/index_all_models.htm

 

[Marcellllo]

Thank ford Advices guys !!

I created this ... what do You think about it ? Are there any mistakes, that You can see and I dont ?

Thank You for Your help

 

[way2bord]

Thank ford Advices guys !!

I created this View attachment 48761 ... what do You think about it ? Are there any mistakes, that You can see and I dont ?

Thank You for Your help

I strongly encourage you to wiki "Normalization" and read through the page until you feel you understand it.

- Delete all your skill tables.
- Replace fields in tbl_User-Skills with:
User_ID (foreign key - joins contractor to skills)
Skill_ID (foregin key - joins skills to contractor


- Create Skill table
SkillID
SkillName (name of skill)
SkillSubLvl (main, sub, sub sub, sub sub sub, sub sub sub sub sub, or detail)
SkillLevel (skill level)

Notice by having a SkillSubLvl field, you can create as many sub levels as you want, simply by changing the value in a cell. It also eliminates the need for 5 other tables.

 

[Marcellllo]

Thank You for quick respond

I had it in way that You suggest, but there is like 600 combinations of all posible skills and I found it too unpractical have them under 600 IDs in case of reporting and so on. Do you have some idea how can I do it ? Because some skills Has only one level for example services but some like database development has four possible subskills and I dont know how to set the tables to make it simple and useful :/

 

[way2bord]

Thank You for quick respond

I had it in way that You suggest, but there is like 600 combinations of all posible skills and I found it too unpractical have them under 600 IDs in case of reporting and so on. Do you have some idea how can I do it ? Because some skills Has only one level for example services but some like database development has four possible subskills and I dont know how to set the tables to make it simple and useful :/

If there's 600 combinations, there's 600 combinations. Whether it's in one column (field), or split between 5 different tables and 5 different columns, you still have the same data. Having just 1 column for all the data keeps it more organized and easier to retrieve/manipulate -- much much easier to report on (think 1 query instead of 5 queries)...


You may want to Normalize further if I understand you correctly:

- Create Skill table
SkillID
SkillName (name of skill)
SkillLevel (skill level)

table SkillSubLvl
SkillSubLvlID (Primary Key - autoid)
SkillSubLvl (main, sub, sub sub, sub sub sub, sub sub sub, detail)

table Skill_Sub
Skill_SubID (Primary Key - autoid)
SkillID (Foreign Key - join Skill to Sub Lvl)
SkillSubLvlID (Foreign Key - join Sub Lvl to Skill)


You would then refer to Skill_SubID to reference your skillset and sublevel of that skillset.

- Replace fields in tbl_User-Skills with:
User_ID (foreign key - joins contractor to skills)
Skill_SubID (foregin key - joins skill and sublvl to contractor)

 

[Marcellllo]

Thanx for explanation

Ok I va made it according to Your advices, but I am not sure how to insert data into new database ... because when I use form it put data in Contractor and in Location, but it do not make connection in User-Location table ... do You have some idea why ? Is there something I might forget to do ?

10x very much

 

[way2bord]

Thanx for explanation

Ok I va made it according to Your advices, but I am not sure how to insert data into new database ... because when I use form it put data in Contractor and in Location, but it do not make connection in User-Location table ... do You have some idea why ? Is there something I might forget to do ?

10x very much

All three tables require independent data input.

Think of it as 2 separate lists
- A list of all your Contractors
(textbox to add new contractor)
- A list of all your Locations
(textbox to add new location)

And 1 final list that links the two
- a list indicating which locations go with which contractors
(2 comboboxes: 1: control source = userID, row source = ContractorName; 2: control source = locationID, Row source = Location Name)

 

[Marcellllo]

Thanx for respond way2bord

To be honest, I dont quite understand Your advice. ( probably because I am rookie but just to be sure )

That 2 separate lists means 2 separate forms for each table ?
And than one form ( list ) to put data in that 2 separate forms ?

Is there a possibility to make one form for all tables for data entry ? Or I need to make a form for each table and than connect them with macro or VBA somehow ?

I have never have so many tables so I really dont know how to make it work

Thanks for Your time and help

 

[way2bord]

See attachment dummy example

 

[Marcellllo]

Thanks for perfect example way2bord !

Wow that is exactly the way I would like it to make it in. But there is only forms for location and user id ...and can I make one from for all data I would like to store in database or do I need to have separate form for each group ?

10x

 

[way2bord]

Thanks for perfect example way2bord !

Wow that is exactly the way I would like it to make it in. But there is only forms for location and user id ...and can I make one from for all data I would like to store in database or do I need to have separate form for each group ?

10x

The example form demonstrates how to enter new data into a standard tables and how to enter data into "join" (juncture) tables.

You'll need similar features for each standard table and each join table...

How you arrange the form or forms really depends on how you want to setup your visual layout. If you want, you can use a single form to capture all data input - or you can separate it into many forms...

Setup your tables first, then try to update my example to fit your tables. Then change it so you have better colors, and font and visuals, etc.