Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19

    Design of DBMS for Clearance

    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:

    Click image for larger version. 

Name:	DoubleEntryTable.png 
Views:	39 
Size:	9.0 KB 
ID:	49903



    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 ?


    Click image for larger version. 

Name:	Cardinality_2.png 
Views:	38 
Size:	20.2 KB 
ID:	49907Click image for larger version. 

Name:	Cardinality_1.png 
Views:	37 
Size:	16.8 KB 
ID:	49908

    many thanks in advance for your replies






  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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,

  3. #3
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Quote Originally Posted by CJ_London View Post
    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.

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    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
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #5
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Quote Originally Posted by mike60smart View Post
    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

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

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

    Also what is Prof ID short for ?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    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?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Quote Originally Posted by mike60smart View Post
    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

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

  10. #10
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Quote Originally Posted by CJ_London View Post
    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?

  11. #11
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    None of your uploaded Relationships would work.

    You need something along the lines of the attached.
    Attached Thumbnails Attached Thumbnails Relationships.png  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    See that attached example which would need additional work.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Quote Originally Posted by mike60smart View Post
    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 ?

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    frmPeople is your Data Entry Form for all of the Entities in your database
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    mackson is offline Novice
    Windows 10 Access 2019
    Join Date
    Mar 2023
    Posts
    19
    Quote Originally Posted by mike60smart View Post
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 26
    Last Post: 11-03-2021, 09:11 AM
  2. Replies: 1
    Last Post: 08-15-2020, 04:34 AM
  3. Design vs Article (=Design+Materials) problem
    By emihir0 in forum Database Design
    Replies: 14
    Last Post: 04-20-2015, 03:03 PM
  4. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  5. what are some benefits of a DBMS like MS Access?
    By helpporfavorplz in forum Access
    Replies: 3
    Last Post: 02-23-2010, 08:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums