Data Model and Data Flow Diagram - Essay Example

Data Model and Data Flow Diagram for PresRX Case: PresRX is a new online pharmacy that wants to provide prescription and non-prescription drugs to Canadian consumers. When a customer goes online, he is shown the drugs PresRX sells. To make the shopping easy, we allow the customer to search our products based on the following categories: pharmaceutical company, product type (prescription or non-prescription), drug type (antibiotics, antidepressant drugs or vitamins or cold/ flu drugs etc.), problems (like allergies or pain relief etc.) and the main ingredient (like penicillin). Each product also has the strength of the main ingredient (e.g. Allegra 30 mg, Allegra 50 mg etc.). The same product with different quantity in each package is treated as a separate product (e.g. Allegra 30 mg with 10 pills is different from Allegra 30 mg with 20 pills). As the customer starts selecting products, the customer is given a "shopping cart". At the end, the customer can check out the shopping cart. At this time, he will be asked for personal information as well as login information for future purposes. The personal information includes the email, gender and date of birth in addition to the usual personal information needed for online shopping. He is also asked for credit card information (PresRX accepts only Master, Visa and American Express). Since PresRX also sells prescription drugs, if the "shopping cart" for the customer contains a product that is available only by prescription, the customer must provide the physician's information as well. This information will include the physician's name, address, specialty, phone number, email, fax number and hospital/clinic affiliation (we care about only the clinic that our customer goes to). (If the physician's info is not provided, we will contact the customer and obtain the info.) The customer can also select the shipping method: we provide overnight, standard air and standard ground. For some of the drugs that are time and temperature sensitive, the customer will not be able to select the shipping method: the shipping method will automatically be chosen. The products will always be shipped to the person who orders them. If the customer wishes to use his insurance to make the purchase, we obtain the insurance company information as well as the insurance number and we will validate it by contacting the insurance company. Once the credit card is validated, we request validation from the insurance company on the prescription, physician on the prescription and validation of the physician from the hospital/clinic (to prevent fraudulent prescriptions). This is done via an automated email sent to the clinic as well as the physician. If emails are not made available, one of the employees will fax the request. This request will contain the drug requested, the quantity ordered, date it was ordered and the customer's name, address and phone number. The insurance company is called. We do this every time the person shops. The physician's office is given access to a physician's portal where his office can approve the prescription drug order. On approval, the drug is packed and shipped to the customer. The physician's portal is available where physicians can log in to not only approve the prescription request but also to renew the prescription for their patients. We also need to obtain supplies regularly. The reordering is based on need, triggered by low stocks. The system will have to reorder from the supplier via automatic ordering. As the products are shipped to our customers, the system updates the product quantity and as soon as the quantity goes to a pre-set "low" (different for each product), the system will immediately send an order to the product's supplier. Since we handle brand items, each product will have only one supplier. For each product we receive, our employees will update the product quantities in our system. The employees doing this will be the warehouse managers and we need to know who entered which batch of received shipments. Since prescription drugs can be abused, we want to monitor (via regular reports) the hospitals and the doctors that give out most PresRX, the drugs they give the PresRX for and the customers that appear with multiple clinics/physicians for the same drug. We also want report on the orders we place with the suppliers. The main attributes are: CUSTOMER: name, date of birth, gender, email address, address, city and province of residence PHYSICIAN: name, address, physician id info including clinic of practice or hospital of practice DRUG: description DRUG INTERACTION (CAL) - what types of drugs customer could potentially be allergic too DRUG- description DRUG TYPES (CSL) -drugs available ENROLLMENT- enrollment number ORDER SECURITY (SSL)- secure payment information for transactions PAYMENT- customer id, date created, date due, amount due, amount paid and payment method (M/C or VISA) First: Entities: Customer Shipping (since there are two different types of shipping: overnight or regular) Drug ordered Drug type Main drug ingredients and strength Pharmaceutical company Payment method Historical data (to keep a record for the customer and physician's portal for future orders) Drug interactions/allergies Physician (this includes the physician needing to authorize processing of prescription orders through the physician's portal) Next: Attributes: Customer: first name, last name, Date of Birth, Gender, Address, Email, place of residence (city, province, postal code) Physician: ID, first name, last name, address, city, province, postal code, phone number, email, clinic of practice Shipping: Methods (currently two that includes overnight or regular) Drug: Description Pharmaceutical company Historical Report: By customer ID and includes all drugs that have been ordered Drug interactions: allergies, etc. Main drug ingredients (e.g. Allegra 30 mg, Allegra 50 mg) Physician's Portal: by physician ID which includes what has been approved, etc. to eliminate prescription fraud 1. Customer sets up information and any drug interactions are entered into a database that is populated through the answer to a specific question when first registering. 2. The customer is given a shopping card 3. If any shopping cart items contain prescription drugs, the customer must provide the Rx of the physician (an alert is then sent to the physician's portal for validation and approval) 4. The customer can then select shipping methods they would prefer. Those products/drugs that are on time and temperature sensitivity, there will be no further option to select shipping as this will be automatically selected for the customer. 5. If there is insurance company information to be provided, this will be asked and then the insurance company contacted. 6. Credit card information will then be validated and authorized with that information being provided to the customer upon final check out 7. When your bank informs the customer service representative and customer that the order is paid for, the product will only then be shipped out 8. Every order that is created, the information is added to the historical information database for the customer to either have printed out monthly and sent via email or snail mail, or, it can be included each time with the orders. 9. The physician's portal will be alerted for each customer ordering a prescription drug and the customer will be notified that until the physician is contacted and approves the order, the prescription drug part of the order cannot be shipped. The customer can then be given the option of waiting to ship the entire order or do a partial shipment. Explanation Data flow 1: The data first goes into the process Data flow 2: The data comes out after the process has been finished Data flow 3: Data going into the process Data flow 4: Tables being updated and parents being sent the statement after the process of statement creation. Read More