Design of DBMS for Clearance

[mackson]

Hi dear all!
I would like to have a centralized management model via access of clearances that are listed on Excel, in order to better control their updates. The data are in a double-entry Excel table as below:



The database that I want to edit, will allow you to select in a form via the name and surname of a person, the type(s) of clearances (s) that he has and to enter his (their) expiry date(s).
Before digging into the queries I would like to set the cardinality and the relationship based on these rules below:

1-A person has one or more clearances
2-A clearance can be owned by one or more persons
3-a person can have several clearances which belong to a category.
4-Each category can include one or several clearances
5-There are several categories
6- Also: a person can have one or more categories
7-Similarly, a category can be owned by one or more people.
8-We must be able to update the database because:
o The list of authorization types and/or authorization categories can be updated to add new ones.

o New people can be added or old people deleted with the corresponding information (company, entity, personnel number, etc)


For the functionality of the database once the relation are setteled, one should take into account this:
• I would like to add three other modifications to the initial tabel ie the database:

o Change one: There is an “clearance category” , which has a Family/Sub-Family relationship with the “clearance type”. I would therefore like to have a field (or a table) which will record the different Categories (or Families of queries. After the choice of the person, this category will be selected, before selecting the type of associated clearance (Sub-family) to enter its expiry date

o Modification two: rather than having an end of validity date for the type of authorization, I would like to add the date of issue of this type of authorization and its end of validity date which will be a calculated field (date of issue + Year), X = 5 years typically or configurable depending on clearance.

o Modification Three: I want to add the authorization card number of each person in a field or a table. This number is specific to each person who has clerance, and hase to be entered in numerical format [XXXX/XXXX] i.e. - Order No./Year, for example No. 0800/2023.

• I would then like to make requests:

o To highlight expired clearance:

§ Either by Enterprise
§ Or by entity
§ Either per person

o Be able to highlight the clearances that will expire in a certain period of time (either in number of days, or months or years)

o If possible, be able to make statistics on, for example, the percentage of clearance expired and/or not expired

I thought about these two relations ships below; which one is correct with respect to what I stated above ?






many thanks in advance for your replies

[CJ_London]

I'm a bit confused about the rules due to lack of context. What is a clearance? what is a category? What is the relevance of validity? What does acquisition mean? where does authorisation come into it?

can you provide a couple of sentences of what the app is required to do, relating the terms in your rules to those in your tables i.e. I presume a person is an employee but can't be sure,

[mackson]

I'm a bit confused about the rules due to lack of context. What is a clearance? what is a category? What is the relevance of validity? What does acquisition mean? where does authorisation come into it?

can you provide a couple of sentences of what the app is required to do, relating the terms in your rules to those in your tables i.e. I presume a person is an employee but can't be sure,

Hi !
Sorry, yes indeed it’s a bit confusing. Please find below some clarifications
• A clearance is a kind of “authorization”, for instance the authorization to drive a car, a dozer, or a wheel loader.
• A category is a kind of group of specific clearances or authorizations, for instance I can say that a dozer and a loader belong to the category of “heavy equipment” and that a car belongs to the “light vehicle” category.
• The validity is a kind of period during which a clearance is valid. As it is issued at a certain moment, it has to be renewed after a certain amount of time.
• Regarding acquisition table, it is a kind of join table between ‘tableEmployee” and “table clearance” as there is an (n to n) relation between those two tales, because of these statements :”-a person has one or several clearances; -A clearance can be owned by one or several persons”. By doing this I keep my initial request “clearances categories group together clearance and users. The addition of the "join" table between “employee” and “listClearance” makes it possible to know who has which clearance (and therefore, by the relationship, of which category)
• “where does authorization come into it”, it is a misspelling from me, in fact I mean clearance by authorization, so the rule N° 8 means: the list of “clearance” types and/or “clearance” categories can be updated to add new ones.
• Indeed, a person is an employee

In short sentence the database should register through forms information on employees’ clearances or authorizations which belong to categories, based on the rules. Once that achieved, some specific queries will be applied. Let me know if you need more information.

[mike60smart]

I think we need some example data.

Do you currently record this information in an Excel spreadsheet?

If yes, then best to upload an example with any Confidential data removed

[mackson]

I think we need some example data.

Do you currently record this information in an Excel spreadsheet?

If yes, then best to upload an example with any Confidential data removed

This a template as it exist:
DataTest-1.zip
Hope this help!
Regards

[mike60smart]

Hi

Are the Dates Jun-05 etc dates that the Person is Authorised to Drive the Vehicle?

Also what is Prof ID short for ?

[mike60smart]

Also:

1. Can a Person only ever belong to 1 Enterprise
2 Can a Person only ever belong to 1 Entity

Or would you need to keep a History of the various Enterprises and Entities that a Person belongs to?

[mackson]

Also:

1. Can a Person only ever belong to 1 Enterprise
2 Can a Person only ever belong to 1 Entity

Or would you need to keep a History of the various Enterprises and Entities that a Person belongs to?

Hi.
1. A person only belong to 1 Enterprise to keep it simple.
2 A person only belong to 1 Entity, to keep it simple.
Just notice that in both case a person can be transferred to an other enterprise or entity, but it is very very scarce

Are the Dates Jun-05 etc dates that the Person is Authorised to Drive the Vehicle?

Also what is Prof ID short for ?

The dates etc are the dates at which the authorizations have been issued. The idea in the queries, once the database built, will be to calculate the expiry date in another field. Please note that in the example file that the date are fictitious.

Also what is Prof ID short for: it means the registration number of the employee

Regards

[CJ_London]

Just notice that in both case a person can be transferred to an other enterprise or entity, but it is very very scarce

if it can happen you should cater for it. Use junction tables between person and enterprise/entity. Junction table to also contain a start date and an end date if there can be gaps.

[mackson]

if it can happen you should cater for it. Use junction tables between person and enterprise/entity. Junction table to also contain a start date and an end date if there can be gaps.

All these enterprises/entities employe work on the same site under the same rules. So transferring an employee to one Enterprise/entity to another doesn't change much things to his clearance validity unless if he needs new one. What are do you think of the two relationship I posted previously (JPEG files) ? Are they correct?

[mike60smart]

Hi

None of your uploaded Relationships would work.

You need something along the lines of the attached.

[mike60smart]

Hi

See that attached example which would need additional work.

[mackson]

Hi

See that attached example which would need additional work.

Thanks! How does it work ? The entry form to populate the base is the "frmPeople", No ?
To manually "insert data" where should I start ? I can fill the different tables with my data first ?

[mike60smart]

Hi
frmPeople is your Data Entry Form for all of the Entities in your database

[mackson]

Hi
frmPeople is your Data Entry Form for all of the Entities in your database

1. To populate the db with my data I need to fill these different tables (tblEnterprises; tblEntities; tblNrYears; tblPeople; tblVehicles) with my accurate data that can be retrieve from the frmPeople ?
2. The others tables (tblPeopleEnterprises; tblPeopleEntities; tblPeopleVehicles) will be filled through the form once data are added.

Is it correct ?
If yes, I will delete the pre-filled data in the database. If everything fits in, I will try the queries. How do I delete all the data ?
Thansk again