Skip to content

Database

Jump to bottom
David Roodt edited this page Sep 28, 2022 · 14 revisions

Version 1

image

User table

  • UserID - auto generated unique ID for all users
  • email - Email user used to sign up with
  • Password - Encrypted Password stored in DB
  • PasswordSalt - Salt used to encrypt Password

Organisation table

  • UserID - ID used to link Organisation info to User info
  • OrgName - Name of the Organisation
  • NGONum - NGO number for certified NGO's
  • Description - Description of Organisation

Rating table

  • OrgID - UserID of Org that is receiving a rating
  • ClientID - UserID of Client giving the rating
  • Rating - Numeric rating
  • Comment - Comment left by Client about Organisation in review

Version 2

image

User table

  • UserID - auto generated unique ID for all users
  • email - Email user used to sign up with
  • Password - Encrypted Password stored in DB
  • PasswordSalt - Salt used to encrypt Password

Organisation table

  • UserID - ID used to link Organisation info to User info
  • OrgName - Name of the Organisation
  • NGONum - NGO number for certified NGO's
  • Description - Description of Organisation
  • Adress ID - ID of Address stored in Address table

Rating table

  • OrgID - UserID of Org that is receiving a rating
  • ClientID - UserID of Client giving the rating
  • Rating - Numeric rating
  • Comment - Comment left by Client about Organisation in review

Address table

  • AddressID - ID of Address for link to Organisation
  • Address - Address Line 1 of Address
  • Address2 - Address Line 2 of Address
  • City - City of Address
  • Province - Province of Address

Version 3

image

User table

  • UserID - auto generated unique ID for all users
  • email - Email user used to sign up with
  • Password - Encrypted Password stored in DB
  • PasswordSalt - Salt used to encrypt Password

Organisation table

  • UserID - ID used to link Organisation info to User info
  • OrgName - Name of the Organisation
  • NGONum - NGO number for certified NGO's
  • Description - Description of Organisation
  • Adress ID - ID of Address stored in Address table

Rating table

  • OrgID - UserID of Org that is receiving a rating
  • ClientID - UserID of Client giving the rating
  • Rating - Numeric rating
  • Comment - Comment left by Client about Organisation in review

Address table

  • AddressID - ID of Address for link to Organisation
  • Address - Address Line 1 of Address
  • Address2 - Address Line 2 of Address
  • City - City of Address
  • Province - Province of Address

Item table

  • ItemName - Name of Item
  • OrgID - UserID of Org that has this item
  • Description - Short Description of Item
  • Quantity - Amount of Item currently in the Organisation's inventory

Version 4

Schema

User table

  • UserID - auto generated unique ID for all users
  • email - Email user used to sign up with
  • Password - Encrypted Password stored in DB
  • PasswordSalt - Salt used to encrypt Password
  • Identity - Stores whether a user is a, Client, Org or Admin

Organisation table

  • UserID - ID used to link Organisation info to User info
  • OrgName - Name of the Organisation
  • NGONum - NGO number for certified NGO's
  • Description - Description of Organisation
  • Adress ID - ID of Address stored in Address table
  • Profile Picture - Picture for Org. Stored as Base64
  • DateCreated - When the Org signed up for the site

Rating table

  • OrgID - UserID of Org that is receiving a rating
  • ClientID - UserID of Client giving the rating
  • Rating - Numeric rating
  • Comment - Comment left by Client about Organisation in review

Address table

  • AddressID - ID of Address for link to Organisation
  • Address - Address Line 1 of Address
  • Address2 - Address Line 2 of Address
  • City - City of Address
  • Province - Province of Address

DonoItem table

  • ItemName - Name of Item
  • OrgID - UserID of Org that has this item
  • Quantity - Amount of Item currently in the Organisation's inventory
  • Description - Short Description of Item
  • Quality - The quality of Item (New, Used)
  • Type - The Type of Item (Food, Clothes)
  • DonoDate - Date of Donation
  • DonoLoc - Location of Donation

Delivery table

  • DeliveryID - Unique ID for delivery
  • ItemID - ItemID of Item that has this item
  • OrgID - UserID of Org that has this item
  • Quantity - Amount of Item currently in the Organisation's inventory
  • Description - Short Description of Item
  • Quality - The quality of Item (New, Used)
  • Type - The Type of Item (Food, Clothes)
  • DonoDate - Date of Donation
  • DonoLoc - Location of Donation

Final Version

Description of requirements

A USER is defined by either being a ASSIST or NEED party. All USERS require an Email and Password to log in. ASSIST have a Name, NGO Number, Description, Address, Profile Picture and when the Account was Created. Addresses are saved as Google Maps location links. NEED users can RATE ASSIST Parties. RATINGS consists of a Rating number out of 5 and a Comment. ASSIST and NEED parties can send Messages to each other. All previous MESSAGES between to USERS are saved and displayed to the USER. ASSIST can list ITEMS that they have DONATED, when they do the must provide the Item’s Name, Picture, Quality, Description and Type, as well as when and where each ITEM was DONATED. Each NEED Party has an INVENTORY of ITEMS, here it shows the Quantity. USERS can organize a DELIVERY of an ITEM. They must agree upon a Location, Date and Time. DELIVERY and MESSAGES will send Notifications to the receiving USER

EER-Diagram

Relationships are shown in grey image

EER-Diagram to Relation Mapping

Step 1: Strong Entities

User

UserID Email Password

Step 2: Weak Entities

User

UserID Email Password

Item

ItemID ItemName Picture Description Type

Step 3: 1 to 1 Relationships

NA. No 1 to 1 relationships in this system

Step 4: 1 to N Relationships

UserID Email Password

Item

ItemID ItemName Picture Description Type

Inventory

ItemID UserID Quantity

Step 5: M to N Relationships

User

UserID Email Password

Messages

UserID UserID(Assist) PastMessages NotifyAssist NotifyNeed

Rate

UserID UserID(Assist) Rating Comment

Item

ItemID ItemName Picture Description Type

Inventory

ItemID UserID Quantity

Donation

ItemID UserID Quantity Date Location

Step 6: Multivalued Attributes

NA. No Multivalued Attributes in this system

Step 7: N-ary Realasinships

User

UserID Email Password

Messages

UserID UserID(Assist) PastMessages NotifyAssist NotifyNeed

Rate

UserID UserID(Assist) Rating Comment

Item

ItemID ItemName Picture Description Type

Inventory

ItemID UserID Quantity

Donation

ItemID UserID Quantity Date Location

Step 8: Specialliations and Generalisation

User

UserID Email Password

Assist

UserID NGONumber Description Address Profile Picture Account Created

Messages

UserID UserID(Assist) PastMessages NotifyAssist NotifyNeed

Rate

UserID UserID(Assist) Rating Comment

Item

ItemID ItemName Picture Description Type

Inventory

ItemID UserID Quantity

Donation

ItemID UserID Quantity Date Location

Step 9: Mapping Unions

NA. No Unions in the Schema

Normalization

This is a lengthy process here is the result. All tables are in their Third Normal Form. Joins will not be common in this System thus the Database is not in 4th or 5th Normal Form.

Schema

User table

  • UserID - auto generated unique ID for all users
  • email - Email user used to sign up with
  • Password - Encrypted Password stored in DB
  • PasswordSalt - Salt used to encrypt Password
  • Identity - Stores whether a user is a, Client, Org or Admin

Organisation table

  • UserID - ID used to link Organisation info to User info
  • OrgName - Name of the Organisation
  • NGONum - NGO number for certified NGO's
  • Description - Description of Organisation
  • Adress ID - ID of Address stored in Address table
  • Profile Picture - Picture for Org. Stored as Base64
  • DateCreated - When the Org signed up for the site

Rating table

  • OrgID - UserID of Org that is receiving a rating
  • ClientID - UserID of Client giving the rating
  • Rating - Numeric rating
  • Comment - Comment left by Client about Organisation in review

Address table

  • AddressID - ID of Address for link to Organisation
  • Address - Address Line 1 of Address
  • Address2 - Address Line 2 of Address
  • City - City of Address
  • Province - Province of Address

DonoItem table

  • ItemName - Name of Item
  • OrgID - UserID of Org that has this item
  • Quantity - Amount of Item currently in the Organisation's inventory
  • Description - Short Description of Item
  • Quality - The quality of Item (New, Used)
  • Type - The Type of Item (Food, Clothes)
  • DonoDate - Date of Donation
  • DonoLoc - Location of Donation

Delivery table

  • DeliveryID - Unique ID for delivery
  • ItemID - ItemID of Item that has this item
  • OrgID - UserID of Org that has this item
  • Quantity - Amount of Item currently in the Organisation's inventory
  • Description - Short Description of Item
  • Quality - The quality of Item (New, Used)
  • Type - The Type of Item (Food, Clothes)
  • DonoDate - Date of Donation
  • DonoLoc - Location of Donation
Clone this wiki locally