Help with base normalization

[Niktosko]

Hi,
I am trying to solve my problem with calculating salaries in my small company. Have tried to create a database with some tables, forms and queries, but still not sure how to complete the functions I need to get working.

I hope somebody from you guys would tell me how to do it:

I have WORKERS, JOBS, MONEY for JOB. The WORKERS may work on more JOBs a day. On a JOB may work more WORKERS a day. The JOB can be carried out for more than one month. My calculation of salary is made on monthly basis. There is one main CONDITION: by JOBS still not completed, I calculate a fix SALARY PER HOUR multiplied with the sum of hours worked on the JOB. By completed JOB, first I calculate the SALARY PER HOUR by dividing the MONEY for JOB with the total hours worked on the job, than use this value for calculating the total money a particular worker should get for a job and finally I discount all the money I have paid to the worker for a particular job in past months (when the JOB has been qualified as not completed).

Could somebody help me by telling me which TABLES with which ITEMS do I need for this? And maybe to tell me how could I make it working on monthly basis? Maybe I need some Macros for this or some complicated FORMs.

I will appreciate any help. Thanks in advance!

[Villeroy]

You need at least 4 tables with the following keys and relations:
- a list of jobs (auto-PK)
- a list of persons (auto-PK)
- a list of wages where you enter the price of an job starting at a given date (PK includes a date and a job-ID)
- an employment table to map which worker did which job at a given date how many hours (PK includes the job-ID, the date and the person-ID)

[Wages.JobID]n---1[Jobs.ID]n---1[Employments.JobID | Employments.PersonID]n---1[Persons.ID]

Now you can lookup the price for any occupation at any day and who did which job how long and when.

[Villeroy]

The above receipt can be translated to SQL to be run in Tools>SQL against a blank, new embedded HSQLDB:

Code: Select all

CREATE TABLE JOBS(
	ID INT GENERATED BY DEFAULT AS IDENTITY,
	N VARCHAR_IGNORECASE(12) NOT NULL,
	PRIMARY KEY(ID)
	);
CREATE TABLE PERSONS(
	ID INT GENERATED BY DEFAULT AS IDENTITY,
	N1 VARCHAR_IGNORECASE(12) NOT NULL,
	N2 VARCHAR_IGNORECASE(12) NOT NULL,
	BD DATE NOT NULL,
	PRIMARY KEY(ID)
	);
CREATE TABLE WAGES(
	JID INT NOT NULL, 
	D DATE NOT NULL,
	W DECIMAL(5,2) NOT NULL,
	PRIMARY KEY(JID,D),
	FOREIGN KEY(JID) REFERENCES JOBS(ID)
	);
CREATE TABLE EMPLOYMENTS(
	D DATE NOT NULL,
	JID INT NOT NULL, 
	PID INT NOT NULL,
	H TINYINT NOT NULL,
	PRIMARY KEY(D,JID,PID),
	FOREIGN KEY(JID) REFERENCES JOBS(ID),
	FOREIGN KEY(PID) REFERENCES PERSONS(ID)
	);

CREATE UNIQUE INDEX "uniquePerson" ON "PERSONS"
	("N1","N2","BD");
CREATE UNIQUE INDEX "uniqueJob" ON JOBS (N);

This generates the whole database.
Call menu:View>Refresh Tables and then Tools>Relations which displays the resulting database:

Relations window of jobs database

[Villeroy]

OK, so we defined an employment by the 3 PK fields employee, date and the job to be done (PID,D,JID). And we define the wage by the job and the start date(JID and D).
So the following query maps these primary keys to each other:

Code: Select all

SELECT "E"."D" AS "ED", "E"."JID", "E"."PID", MAX( "W"."D" ) AS "WD" 
FROM "EMPLOYMENTS" AS "E", "WAGES" AS "W" 
WHERE ( "W"."D" <= "E"."D" ) AND ( "E"."JID" = "W"."JID" ) 
GROUP BY "E"."D", "E"."PID", "E"."JID"

[get the latest wage-date (WD) being smaller than the employment date for each employment]

From the above record set "qEmploy_WDate" we get all the wages easily:

Code: Select all

SELECT "qEmploy_WDate"."ED", "qEmploy_WDate"."PID", "qEmploy_WDate"."JID", "EMPLOYMENTS"."H", "qEmploy_WDate"."WD", "WAGES"."W", "EMPLOYMENTS"."H" * "WAGES"."W" AS "Amount" 
FROM "WAGES", "qEmploy_WDate", "EMPLOYMENTS" 
WHERE "WAGES"."D" = "qEmploy_WDate"."WD"
  AND "WAGES"."JID" = "qEmploy_WDate"."JID"
  AND "qEmploy_WDate"."ED" = "EMPLOYMENTS"."D"
  AND "qEmploy_WDate"."PID" = "EMPLOYMENTS"."PID"
  AND "qEmploy_WDate"."JID" = "EMPLOYMENTS"."JID"

With the usual receipts from this forum we get a filterable form with wages for each employment and the sum of all wages.
Based on the above query we can create several queries and reports based on those queries.
The attached Base document contains the 4 above outlined 4 tables plus one extra table to store filter criteria, several queries and reports together with a filtered form and some subforms. There is not a single line of macro code involved but it is completely impossible to get this database from the Base wizards. Almost nothing has been created using the wizards.

[Villeroy]

https://issues.apache.org/ooo/show_bug.cgi?id=121160
2 of the 3 "old style reports" are not filled with data. Only the monthly report works as expected. You may install the report builder extension and create another set of "new style reports" from the respective queries (right-click query>Create Report...)

[Niktosko]

Ok. So far clear. If I understand I can set the price of an employment for each day different. What I need more is:
I need the wages to be calculated with help of ratio:PRICE FOR WHOLE JOB<defined by EMPLOYER>/SUM OF HOURS<ALL WORKERS worked on this JOB in TOTAL>, lets call it value A. In the month, in which the JOB is not completed, we are not able to tell the SUM OF HOURS for this job. Thats why I pay an agreed SALARY for HOUR and multiply this value with the number of hours worked on job. In the month, in which the JOB has been completed, we are able to count the SUM OF HOURS for a job. Than we will pay to the worker SUM OF HOURS DONE on JOB * value A - SUM OF ALL SALARIES PAID FOR THIS JOB (in recent months).

If I am correct, this means I need aditional Table with Payments. There I will make an evidence of all MONTHLY payments to each worker for each job and in case some job is completed in the month, there will be used this special formula for the payment of a completed job. I would like the base would make this automatically.

[Villeroy]

Ok. So far clear. If I understand I can set the price of an employment for each day different.

Or each year. If the price for the workforce would be saved with the job, all prices of the past would be wrong after you entered a new price.
In my blueprint the jobs table is supposed to store only the human readable name of a profession and its automatically generated number. I interpreted a "paid job" as "profession" such as carpenter, mason and mechanic. But that's a rather European view on labour force.
My wage table stores the info that Job #12 (say "carpenter") costs 13,48 since 4th of September 2012 until another price is entered for the same job.

Thats why I pay an agreed SALARY for HOUR and multiply this value with the number of hours worked on job.

Thank you for the clarification. I think you want to set my jobs table (professions) into another context (client orders). Release relations to the wages and to the employments.
The wages table gets the persons-ID instead of the job-ID so the wages table stores the information which person gets which salary from a given day on until another price is entered for the same person.
The employments table still describes how many hours one person works for one job. But now the price is determined by the salary agreement with the worker rather than the job and the job-ID refers to the order of some client.

In my blueprint, all the item lists with some auto-ID have a bare minimum of fields. Only the IDs are important to describe the relations in the normalization process. Of course your job description should include more than a name field and a person is more than 2 names and a birthday but these 3 suffice to describe a person uniquely. The other tables without auto-ID map item-IDs to each other and to some date (wage, employments).

Next step could be a list of clients with a relation to the jobs and a mapping table of client payments (date, client-ID, job-ID and payment).

[Villeroy]

Forget my above rambling. I started a text editor and wrote down the following database jobs2.odb:

Code: Select all

    CREATE TABLE CLIENTS(
       ID INT GENERATED BY DEFAULT AS IDENTITY,
       N VARCHAR_IGNORECASE(12) NOT NULL,
       PRIMARY KEY(ID)
       );
    CREATE TABLE JOBS(
       ID INT GENERATED BY DEFAULT AS IDENTITY,
       N VARCHAR_IGNORECASE(12) NOT NULL,
	CID INT NOT NULL,
       PRIMARY KEY(ID),
	FOREIGN KEY(CID) REFERENCES CLIENTS(ID)
       );
    CREATE TABLE PERSONS(
       ID INT GENERATED BY DEFAULT AS IDENTITY,
       N1 VARCHAR_IGNORECASE(12) NOT NULL,
       N2 VARCHAR_IGNORECASE(12) NOT NULL,
       BD DATE NOT NULL,
       PRIMARY KEY(ID)
       );
    CREATE TABLE PAYMENTS(
       ID INT GENERATED BY DEFAULT AS IDENTITY,
       D DATE NOT NULL,
	JID INT NOT NULL,
	AMOUNT DECIMAL(8,2) NOT NULL,
	PRIMARY KEY(ID),
	FOREIGN KEY(JID) REFERENCES JOBS(ID)
       );
    CREATE TABLE WAGES(
       PID INT NOT NULL,
       D DATE NOT NULL,
       W DECIMAL(5,2) NOT NULL,
       PRIMARY KEY(PID,D),
       FOREIGN KEY(PID) REFERENCES PERSONS(ID)
       );
    CREATE TABLE EMPLOYMENTS(
       D DATE NOT NULL,
       JID INT NOT NULL,
       PID INT NOT NULL,
       H TINYINT NOT NULL,
       PRIMARY KEY(D,JID,PID),
       FOREIGN KEY(JID) REFERENCES JOBS(ID),
       FOREIGN KEY(PID) REFERENCES PERSONS(ID)
       );

    CREATE UNIQUE INDEX "uniquePerson" ON "PERSONS"
       ("N1","N2","BD");
    CREATE UNIQUE INDEX "uniqueJobName" ON JOBS(N);
    CREATE UNIQUE INDEX "uniqueClientName" ON CLIENTS(N);

New, blank database
menu:Tools>SQL... paste and execute
menu:View>RefreshTables
menu:Tools>Relations looks like this:

employees, employments, wages, jobs, clients, payments

[Niktosko]

Sorry for delay.
Thanks for all your posts. But...
What I miss is the automatic function, whih makes the calculation at the end of each month. This function should count the wages for the persons. The algorithm of it I have described in my previous posts. Can you help me with this?

[Villeroy]

My first database draft includes the calculation (but for another set of relations). Use the filter on top of the input form and you see the sum of wages for the filtered record set at the bottom.
Query "qReportMonth" and the equally named report gets the monthly wages for each worker.