[Solved] Route "a" or Route "a" and Route "b"

[dreamquartz]

Hi All,

Seem not to be able to figure out the following:
"a": Sometimes a Person is Authorized to do certain work, based on a Training they received.
"b": In other cases the same Person is only Authorized to do other work, based on a Training and an Audit.

It is possible that the Training is situation "a" is the same Training as in situation "b".
It is possible that one and the same Person can follow either Route "a" or Route "a" and Route "b".

I want to use the same Person Info from tPerson, to determine who is Authorized.
I need to have one Query that can answer either Route "a" or Route "a" and Route "b". At this point the PersonID is provided in 2 separate columns, while I need it in the same column.

How to solve this issue?
Attached a picture, describing the part of the DataBase.

[keme]

I do not fully understand your data structure, but one thought: Can you solve it by having one "void audit" (No audit performed/required/applicable) entry in your database (in tAudit or tTypeOfAudit)?

[eremmel]

Your database list two tPerson tables (?).
Suggestion is to use a sub query with UNION. E.g.

Code: Select all

..... 
FROM (
    SELECT ... FROM tPerson for-route-a
    UNION
    SELECT .... FROM iPerson ... for-route-b
) as "PersonAuthorization"
.....

The union filters out any dubble rows.

[dreamquartz]

I do not fully understand your data structure, but one thought: Can you solve it by having one "void audit" (No audit performed/required/applicable) entry in your database (in tAudit or tTypeOfAudit)?

Hi keme,

I was thinking about something similarly, but the process is approximately the following:
A Certificate is entered and is recorded to a person.
An Audit is entered separately and is recorded to a person.

An Audit can only be done, the moment that there is a Certificate to which an Audit can/will be performed.
The DataBase verifies if a TypeOfAudit does exist, and if it is valid.
The DataBase also checks if there is a valid Certificate to which the Audit must be performed.
A person can only be authorized if both are valid.

Certificates can have 2 different values:
1. On its own certain Authorization can be provided
2. The same certificate can, in combination with an Audit, provide an other additional Authorization.

Your suggestion might indicate that the person who enters the data, either Certificate and/or Audit, is also responsible for validating the Certificate, and that person might not have the Authorization and/or knowledge to perform the task other than entry of the data.

The choice was made not to go that route during development.

Thanks again,

Dream

[dreamquartz]

Your database list two tPerson tables (?).
Suggestion is to use a sub query with UNION. E.g.

Code: Select all

..... 
FROM (
    SELECT ... FROM tPerson for-route-a
    UNION
    SELECT .... FROM iPerson ... for-route-b
) as "PersonAuthorization"
.....

The union filters out any dubble rows.

Hi eremmel,

Thanks for the idea.
I am thinking that way, but am/was hoping that there is/was also a different approach possible.

We are using something similar in a different situation, and that is comprised of even 3x a "UNION".
It takes a relatively long time to get an answer, and is quite complex.

I have searched for a long time, but there is no info on something like this, other than your suggestion.

The second table tPerson is only for clarification, but in reality there is only one tPerson.
The route "a" or "a" and "b" can be applicable at the same time to the same Person even, when filtering on a person.

See also my comments to 'keme'.

Dream

[eremmel]

One has to made a trade off between:
A: Bring the data model in perfect line with the business requirements (almost no business logic needed. 'all' rules in SQL)
B: Have a pragmatic data model with on top some business logic (hard to implement with OO due to need of writing macros).

If you start with A you will always end up with B due to changing requirements, because database model migration is too expensive or just not possible (100+ TB, 24/7 operation etc).

You already found out what the impact A has on SQL. An escape might be materialized auto-updatable (indexed) views for certain queries, but this is just Base unfortunate without a professional database...

[dreamquartz]

One has to made a trade off between:
A: Bring the data model in perfect line with the business requirements (almost no business logic needed. 'all' rules in SQL)
B: Have a pragmatic data model with on top some business logic (hard to implement with OO due to need of writing macros).

If you start with A you will always end up with B due to changing requirements, because database model migration is too expensive or just not possible (100+ TB, 24/7 operation etc).

You already found out what the impact A has on SQL. An escape might be materialized auto-updatable (indexed) views for certain queries, but this is just Base unfortunate without a professional database...

You solved my problem.

Thanks,

Dream

[eremmel]

To some extend it is obligatory to share a solution found with community help, because you like also to learn from questions of others.
I'm sure that you can formulate at high level what you did.

[dreamquartz]

To some extend it is obligatory to share a solution found with community help, because you like also to learn from questions of others.
I'm sure that you can formulate at high level what you did.

Sorry about that.
We used UNION, as you suggested.
We have 2 Queries, one leading to a resultset for all types of Training, and their resulting Qualifications, including when a person needs to be re-qualified, i.e. every 3 years.
The other is depicting the resultset for Audits, indicating how long an Audit is valid, i.e. 5 years.
These 2 resultsets can overlap for a certain person on a certain date.

The 2 resultsets are validated against a certain date, either in the past (for historical purposes) or in the future.
We used UNION to find the overlapping persons and dates.

Dream