[Solved] Route "a" or Route "a" and Route "b"
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
[Solved] Route "a" or Route "a" and Route "b"
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.
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.
Last edited by dreamquartz on Tue Dec 27, 2016 9:11 am, edited 1 time in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Route "a" or Route "a" and Route "b"
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)?
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: Route "a" or Route "a" and Route "b"
Your database list two tPerson tables (?).
Suggestion is to use a sub query with UNION. E.g.
The union filters out any dubble rows.
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"
.....
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Route "a" or Route "a" and Route "b"
Hi keme,keme wrote: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)?
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
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Route "a" or Route "a" and Route "b"
Hi eremmel,eremmel wrote:Your database list two tPerson tables (?).
Suggestion is to use a sub query with UNION. E.g.The union filters out any dubble rows.Code: Select all
..... FROM ( SELECT ... FROM tPerson for-route-a UNION SELECT .... FROM iPerson ... for-route-b ) as "PersonAuthorization" .....
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
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Route "a" or Route "a" and Route "b"
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...
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...
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: Route "a" or Route "a" and Route "b"
You solved my problem.eremmel wrote: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...
Thanks,
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: [Solved] Route "a" or Route "a" and Route "b"
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.
I'm sure that you can formulate at high level what you did.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 881
- Joined: Mon May 30, 2011 4:02 am
Re: [Solved] Route "a" or Route "a" and Route "b"
Sorry about that.eremmel wrote: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.
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
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.