Tables designed suggestions

[Tamrak]

Hello,

I am trying to design a new database and I would like to ask your some suggestions.

I have two groups of employees, part-time and full-time. The conditions of each group are:

We will not have more than 50 part-time employees and not more than 300 full-time employees. System should not accept any additional inputs beyond the numbers indicated.

My questions are:

1. Is it logical to create two tables for each type of employees or just one table?

2. I would like to issue a unique employee number to identify part-time and full-time. I might start with P+Record number. However, it cannot go beyond 50. I can do this with two tables.

If I would like to combine the employees into one table, how can I distinguish and system can stop accepting the input, when it reaches something like P51 or F301.

I would like to know whether it is possible to do it on one table. If it does, can you provide some suggestions of what to do? Thank you.

 

[Tranman]

Tamrak,
Logic would probably dictate that you have only 1 table, which includes something like an "Employee Classification" column, which could be "P" or "F".

As to limiting the employee number to < 300, what are you going to do when employees leave and new ones take their place? Re-issue the same number? If so, what happens to payroll history; etc, which is associated with the former employee? Usually, you would want one employee number to be associated with the same employee, whether they are current or former employees. What if an employee leaves and returns? Do they get a new number? Just some things to think about.

It is really difficult for me to understand why you want to limit the total employees to 50/300. I have a picture on my wall at home, of Microsoft in 1982 or so. Looks like about 10 people.

My point is that you just never know what's going to happen, and artificial limitations you put in your code tend to show up later as really ugly, "I-can't-believe-I-was-that-stupid-not-to-anticipate-this" problems. The impossible: "We will not have more than 50 part-time employees and not more than 300 full-time employees.", tends to happen on a fairly regular basis in real life.

Good luck with your project.

Tranman

 

[ZmrAbdulla]

Limiting Record numbers to a certain number is happening normally in an evaluation version of an application. You can do it by using Dcount() Function

Private Sub CmdNew_Click()
If DCount("EmpID", "tblEmployees") >= 300 Then
    MsgBox "You can't add more than 300 employees"
Else
    'Add new employee
End If
End Sub

Zameer Abdulla
Visit Me (New Look & style)

 

[lespaul]

you may also be able to limit it by including an "ActiveEmployee" field, ie only count those where Active = True.

Leslie

In times of universal deceit, telling the truth will be a revolutionary act. - George Orwell

 

[willir]

Tamrak

I concurr with some of the excellent postings...
- one table (don't use one table for FT and another for PT)
- employee type, or category to distinguish between full and part time
- definitely include boolean yes/no field to indicate that the employee is active or inactive
- limiting the records to 300 + 50 can be achieved using the BeforeUpdate events BUT BUT BUT this will cause problems later on.

The only other thing to consider is if you need to restrict some information to some users. For example, phone number, address, date hired available to general users, but other information such as performance review or salary info to be restricted.

One approach to achieve this is to use two tables - one for general use and the other for the restricted information, and use a one-to-one relationship between the two tables.

For further reading...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard