Degrees and employees (1 Viewer)

Jonny

Registered User.
Local time
Today, 12:46
Joined
Aug 12, 2005
Messages
140
Each employee may have various degrees from different dates.
How correctly to to build a table so that same employee will not get already existing degree?
 

Mark_

Longboard on the internet
Local time
Today, 02:46
Joined
Sep 12, 2017
Messages
2,112
Please post what you already have and how you determine "existing degree"?
 

theDBguy

I’m here to help
Local time
Today, 02:46
Joined
Oct 29, 2018
Messages
10,689
Hi. Sounds like you might need a junction table with a composite primary key field.
 

Jonny

Registered User.
Local time
Today, 12:46
Joined
Aug 12, 2005
Messages
140
Have a table with:
ID (key), EmpID, DegDate, Degree.
How restrict that for a same EmpID will not be added same Degree.
 

Mark_

Longboard on the internet
Local time
Today, 02:46
Joined
Sep 12, 2017
Messages
2,112
Do you have a list of "Degrees" that you can choose from? if so, I'd have a drop down on it. If not, you should be able to check the DegDate and make sure they don't add two degrees for the same date.
 

theDBguy

I’m here to help
Local time
Today, 02:46
Joined
Oct 29, 2018
Messages
10,689
Have a table with:
ID (key), EmpID, DegDate, Degree.
How restrict that for a same EmpID will not be added same Degree.
Hi Jonny. I agree with Mark. I also think you need a table structure similar to the following:


tblEmployees
EmpID, pk
etc...


tblDegrees
DegreeID, pk
etc...


tblEmpDegrees
EmpID, fk, pk
DegreeID, fk, pk

DegreeDate
etc...


In tblEmpDegrees, you can create a primary key based on both the EmpID and DegreeID fields together.
 

Jonny

Registered User.
Local time
Today, 12:46
Joined
Aug 12, 2005
Messages
140
Do you have a list of "Degrees" that you can choose from? if so, I'd have a drop down on it. If not, you should be able to check the DegDate and make sure they don't add two degrees for the same date.
The data is added to that table from the from, so drop down may be required on the form.Still the question is same "how I make sure" not to add two same degrees for the same employee?
Is it something that should be configured in Index propeety?
 

Mark_

Longboard on the internet
Local time
Today, 02:46
Joined
Sep 12, 2017
Messages
2,112
A unique index on Employee and date would prevent you from having two degrees issued the same date.

If you use a list of degrees, you can Join it to your table of degrees and exclude all where there is record for this employee. This means your users won't see it in the drop down list.
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 04:46
Joined
Feb 28, 2001
Messages
17,008
More specifically, the reading topic is "compound index" - which you want to make unique. It does not have to be the PK of the table for that to work.
 

Jonny

Registered User.
Local time
Today, 12:46
Joined
Aug 12, 2005
Messages
140
More specifically, the reading topic is "compound index" - which you want to make unique. It does not have to be the PK of the table for that to work.
Never heard before.. Seems as that's exactly what I was looking for. Indexes is the solution!
Thanks
 

theDBguy

I’m here to help
Local time
Today, 02:46
Joined
Oct 29, 2018
Messages
10,689
Never heard before.. Seems as that's exactly what I was looking for. Indexes is the solution!
Thanks
Hi Jonny. Glad to hear you're all sorted out. Good luck with your project.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom