Project: Cloud Database Administration Solution (basic)

By My Ho

Project Data Source Github click here

Introduction

This is my simple sample project from advanced Python class after 8 months learning my MS Data Analytics program as a brand new learner in this domain. This project showcasing my qualification in:

- SQLite/MySQL (create table, insert, join, and request data)

- Python (using sqlite3, socket, OOP with classes creation, etc.)

- Understanding of database, application of programing in businesses operation, logical thinking and problem solving mindset.

This is a small simple project as a primary ideas for those who is interested and wish to develop further. I myself also does have limitation in networking, API, GUI creation, which does not allow me to provide a fully complete system/program in the mean time, but I do hope this humble idea might bring you inspiration or at least showcasing my qualification.

Problem

This idea coming from my former experiences working in Sharepoint with clients from all over the world and also team-mates from other countries. We were using cloud database for internal work (files, folders, etc.) and sharepoint for sharing data with customers/clients.

Everything was so professional at first when we all could save so much time sharing data to everyone in the team, conveniently editing data directly from cloud and receiving notification for any interaction that might have happened to any file or folder, or even limit access of any specific member to confidential data.

However, overtime its cons start to arise one by one. Our new team member (or even current team members) can't cover all content existing in our cloud database. The database start to grow remarkably over time such as those clients' standard operational procedure (SOP), quotation sheet, regulations, ad-hoc report guildlines, etc.

Enterprises annual operation changes over time whenever new operational activities arise (due to enterprises annual development, procedure improvement, external environment factors change causing changing in affected companies for change management adaptation, etc.) which cause SOP to be updated/revised many times in the same cloud sharing hub. Sometimes those new SOP regulation will only take effect from a specific date in upcoming future, sometimes it is considered to take effect immediately, or sometimes it is only shown as draft. And in order to know if one will be eligible, user must open this file and scan for its validation date which takes quite many steps.

New revised files, updated files, new files overtime will pipe up causing employees hard to capture the overview of their team cloud database. How many files are there in their team database? What are their function/description? Is there any database could be helpful or prioritized for new member self-training? Where are those files within those maze of folders and other files? And this project inspiration was from such experience of mine with hope that his will be inspiration for others working in business customer experience improvement, or who is looking for way of improving company operation.

Figure 1: Project overview

Current cloud database administration problems

Cloud and sharepoint files was saved unorganized with low possibility of management by team leader, manager, high level manager which was caused by:

- Mistakenly upload/revision/delete.

- Employees saved unneccessary in cloud for instant sharing with others but forget to delete.

- Revised files saved but old version files was still saved on cloud for future reference.

- Too many folders caused mistakenly saved files to correct agreed location.

This might cause:

- Occupy unnecessary cloud capacity.

- Waste time in finding the expected files.

- Useful files exist but everyone forget about it or new employees don’t even know if it exists.

For such problem, the idea of this program is to provide enterprises’ end-users with an overview of files existing in their cloud database along with its general information (file name, file id, file type, function/description, etc.), and provide end-user with a useful interface where they can just quickly retrieve file from cloud by providing file id, file name or any unique/identical features of it.

Project Technical Content

Back-end sample database

This database will store all data related to file including file ID, name, description, types, URL for end-user demand of master Excel file request in case end-user want to know all files their team is having in all cloud storage services (teamsite, onedrive, etc.). In actual practices, this database can be bigger with more fields/entities to provide viewer with even more detailed data of the specific files. (Figure 2, 3, 4)

Figure 2: Tables one to one relationship with storage_id as primary key

Figure 3: Table storagelocation sample content

Figure 4: Table storedocuments sample content

Program syntaxes and execution

Project expansion potentiality

Above script is only simple first step to present on how Python connect database and cloud (Onedrive for this specific case). Some of my ideas for project expansion would be:

- Adding function to login server or having APIs free access to secured server so that Python could also retrieve file body content or download file or files from cloud referencing by end-user input file id or name.

- Look into cloud HTTP to see if there is way to retrieve data about files modification in cloud and automatically update the changes in database (such as changed file location, change file name, new file upload, file delete, file revision, date time of revision/delete/modification, account shich interact with the file, etc.). For Sharepoint, programmer can utilize Sharepoint Graph API’s activities function.

- Programmer can build loop condition for end-user in case they are not only wish to trigger 1 but 2 or more files at the same time.

- For access limitation/constraints, programmer can create another SQLite table such as Access table which could include list of AccessPassword, UseraccountID, Userposition or User level of title with which user could provide their employeeID, department, position level (team leader, manager, director, CEP, etc.) to be provided with a password and database will update such account ID along with such password will be set condition for a limited access ability to some specific files base on their level of responsibility in the enterprise.

- Such table could be connected with others in the whole database (employee, department, storedocuments, storagelocation, etc.) for report triggering.