Week 1 – Database Justification Statement
Company: OrGlam!c Skin and Hair Products
This document will discuss the feasibility of developing and implementing a database management system (DBMS) for OrGlam!c, Corporation that will help track and manage their customers, products, billing and employees in an efficient and accessible way while providing support to plans for expansion and global reach.
OrGlam!c is a Florida (USA) based manufacturer of custom handcrafted and 100% chemical free skin and hair care products. The company is owned by Glamar Lopez, founder and CEO. Throughout the years, Ms. Lopez has been handcrafting her own personal care products, drifting away from commercial, chemically altered products that are believed to cause more harm than good to skin and hair. She started sharing her recipes through social media, having a great success and followers on her social media accounts. After a few years of perfecting her natural blends for skin and hair care products through extended research and testing with family and friends, she decided to start her own small company, handcrafting products tailored to each customer’s need and selling them through independent market websites such as Etsy and eBay. This proved to be a great success, taking her business from a small, part-time source of extra-income, to a self-sustainable business where she was able to quit her job and dedicate entirely to continue producing products to fulfill the crescent demand. This is when OrGlam!c corporation is born.
Enterprise’s Goals and Objectives
To become globally recognized as a high-quality organic products manufacturer that represents purity and quality in personal care products, utilizing raw certified organic and vegan ingredients while avoiding any harmful chemicals or preservatives, staying committed to environmental values as well as refusing to test their products on animals.
• To be a globally recognized brand of natural personal care products.
• Promote awareness of the benefits of utilizing natural personal care products.
• Contribute to environmental preservation.
• High quality manufacturing and research.
• A creative marketing and PR program.
• The successful launch of a OrGlam!c Web presence with an eCommerce tool where customers can purchase directly through the Internet.
Keys to Success
• Quality natural products.
• Easy access to products catalog and order processed.
• Strong Web Presence.
• Product logistics and quality control.
• A strong retail presence in eCommerce.
• The creation of a “buzz” about this completely natural brand among industry leaders’.
Nature of Business Environment
Skin and body care products will be developed and produced at main facility, located in Florida, USA, as well as strategically located distribution and manufacturing plants throughout European and Mediterranean continents, with further expansion plans to Africa and Asia as well. OrGlam!c will have two product lines, skin care and hair care. Within the skin care line, a wide array of products will be available including, but not limited to cleansing creams, facial scrubs and masks, shower gels and body lotions, tailored to customer’s needs. Within the hair products line they will offer shampoo, conditioners and hair masks, among others. The pricing strategy is to position products with a competitive price that is in the mid to lower quadrant of other high-quality products and brands competitors. This will be accomplished by carefully studying and comparing competitors’ market offers and emphasizing on the quality and purity of their materials and packaging.
OrGlam!c will occupy a unique market position, since no other brand offers a handcrafted, customer tailored line that includes completely natural and chemical free skin and hair care products. However, with the most recent increase of a natural approach to skin and hair care, as well as DIY (do-it-yourself) tutorials available on the internet, competition does exist. Among the existing organic skin care brands, we can find: ESPA, Goop by juice beauty, Herbivore Botanicals, Kora Organics as of organic hair brands we can find: Soultanicals, John Masters Organics, Alikay and Prim Botanicals. These brands are primarily sold through department stores and unique SPAs, as well as through the internet.
OrGlam!c’s targeted end user is mostly female professionals between the ages of 24 and 65, with mind and body wellness being an important part of their lifestyle. They are also concerned about social and environmental issues.
OrGlam!c products pricing varies according to the ingredients utilized in the creation of their products. Since these are tailored to each customer needs, pricing will be based in raw materials instead of product type.
Enterprise expected growth
OrGlam!c’s plans of globalization, will provide an unprecedented growth of their business that will include expanding operations as well as workforce substantially. Moving from being independent social marketplaces to having a strong eCommerce Internet presence will produce a substantial growth. Up to this date, the company has been a sole person managed organization, never having the needs of additional employees.
The expected company growth, as well as globalization plans, demands an expanded workforce, to be able to fulfill production and distribution needs, as well as provide customer support and further research for product customization. Therefore, a proper database is imperative under this new scenario. Also, detailed records of customers, including skin and hair issues, addresses and purchases will need to be maintained as well. With a properly maintained DBMS there will be a complete customer profile available to be able to tailor their products accordingly.
Having an automated database that tracks customer billings will be also beneficial for this company. Given the projected continuous growth of the company, it will be vital to have accessible and trustworthy data on purchases and billings.
Global business initiatives and related projects
The organization will create a Web Portal that will allow the users to create and update their customer profile so that their information can be utilized to in their custom products creation process. This Portal will also serve as a powerful marketing tool, providing vital information of the organization, as well as a product promotion, while offering the costumer the tools to be able to process and track their orders.
Global Distribution and Manufacturing Centers
Distribution and Manufacturing centers will be strategically located initially at various countries among Europe and the Mediterranean Regions, with plans of expansion to Africa and Asia within 5 to 8 years. This expansion will significantly reduce shipping costs of raw materials and finished products, passing these savings to customers.
Call and Chat Center
A centralized Call and Live-chat center will be established at their main Factory in the United States that will be available globally to answer questions and provide information to customers when requested. These will also provide Customer Service for current and prospective clients as well, handling product orientation and any order related issues.
The company currently relies on a simple MS Access database that is complemented with several spreadsheets. Given the expansion plans they will need a DBMS capable to continue growing with the company, that will provide accessible tools that allows access and maintenance of data globally, through web-based applications. Now, this Access database is physically located at the owner’s computer and is accessible only when this computer is up and running. A dedicated server would be a better choice for the projected company growth.
Drawbacks of the present system include:
• No continuous access to data.
• Reports are not readily available.
• Tracking and maintenance of records can’t be performed on real time.
• No automatization of processes.
• No web-based applications or access to support a robust E-commerce platform.
• Order processing takes longer due to lack of immediate information.
• Not centralized data as pieces are stored in different spreadsheets and access tables.
To be able to keep up with the demands and requirements of OrGlam!c expected growth, a better database management system (DBMS) needs to be implemented. The company will need to keep good records of its employees, raw material inventory, as well as customers and billing. An MS SQL Server database will provide the tools, as well as the capability of growth that this organization will need to support their global operations.
The proposed relational database system will include, but is not limited to, the following tables:
· Customers – Will gather client contact information and personal details, skin and hair issues, preferences and additional notes.
· Orders – Will maintain a record of products ordered by each customer.
· Order details – Will provide specific data on the products ordered by the customer.
· Products – To provide product details.
· Inventory – To provide a list of available raw material for product manufacturing.
· Vendors – Will gather vendors and suppliers contact information and services/products provided.
· Shipping – Will track shipping information per product/order.
This database should reside on a dedicated server and will be accessed by management and employees through network connection. This database will also need to be accessed through the Internet to support an eCommerce platform; therefore, having a web-based data processing application will greatly improve database performance.
Advantages of the proposed system includes, but are not limited to:
· Accessibility and centralization of data.
· Rapid flow of information to improve e-Commerce performance.
· New features and capabilities, including faster reporting and more accurate billing, product database and customer tracking.
· Paperless processes that will comply with the organization’s safe environment goals.
Many critical business decisions depend on the accuracy and availability of data. OrGlam!c’s projected growth and globalization calls for a DBMS that will provide the proper tools to manage their data while providing a centralized access to simplify the processes. The proposed application will provide the needed support and will greatly contribute in a more organized global business operation.
Week 2 – Database Model
Logical Data Model
As previously discussed, OrGlam!c currently has an MS Access database that are complemented with MS Excel spreadsheets, therefore existing data is organized as follows:
The current database lacks detailed data and provides repetitive and sometimes unrelated data fields within datasheets, making tracking and reporting quite difficult tasks to achieve. These datasheets were break apart into smaller, yet more detailed tables, that will improve the quality and integrity of data that is important to the company, as well as accessibility. Having more specific data will help generate important reports that will improve OrGlam!c’s operations as well as support their globalization plans.
As we can see in Figure 2, orders datasheet contains data on orders as well as some customers data, that might be of better use at the customers datasheet. To normalize this into 1NF, customer information that was included at orders datasheet was removed and added to existing customers spreadsheet. On the same note, redundant data on purchase and inventory tables (Figures 5 and 7) was removed from inventory, since it is more directly related to purchases. For 2NF Order ID attribute was removed from products (Figure 4) as it was not related to the data on this spreadsheet and already exists at orders datasheet (Figure 2). To normalize to 3NF additional tables were created to record more detailed data on orders and purchases (Figures 2 and 7), also a shipping table was created to remove shipping information from orders table.
Normalizing the data will assist in providing a far more complete and reliable data scenario that will help OrGlam!c’s management make more informed decisions about daily operations as well as global expansion.
Having a detailed DBMS, that contains the data needed to be tracked by the company, will substantially improve operations, as well as facilitate current and future expansion plans. Availability of high quality and reliable data, which is easily accessible, will facilitate reporting, strategy planning, billing and inventory processes. Also, it will greatly provide a solid foundation for automation of processes.
Week 3 – Database Performance
Physical Data Model
Further details on data model
Below are some examples of constraints and/or indexes contained within this data model. It is important to keep in mind that these may vary or be modified during and/or after implementation process, to better fit the hardware environment, memory, concurrency and needs of the organization.
• Values entered within a primary key attribute must be unique not-null.
• “Ing_Inventory” table will contain a Check constraint (UnitsinStock ; Re_OrderLever) on to identify when Units in Stock have reached reordering levels specified at “Re_OrderLevel” column.
• On Products table, columns “ProductDescription” as well as “ProductIngredients” may contain non-unique values, as ingredients and description of products may repeat depending on product customization.
• Columns identified as Foreign keys that belongs to a many to many or one to many relationship, such as “IngredientID” on “Vendors” table, will not contain unique values. This is also true for Customer-Order relationship.
As discussed during the design process of our logical data model, the need to have reliable and high-quality data available with ease of access, was one of the main requirements of this company, as it would greatly assist in different phases such as strategy planning, audits and reports, billing and inventory processes, while serving as foundation for processes automation.
This physical data model complies with the company’s requirements, providing a robust database, not only containing much needed data, relevant to company’s goals, but a database product that is also easily accessible to internal and external users, facilitating daily operations as well as globalization efforts. In addition, taking into considerations the requirements and constraints that the organization has, such as Inventory re-ordering point, shipment tracking, Product ETA, it will help facilitate the provision of automated processes.