Database Entity Models

Outline

The core features team will be utilizing a database that will tie all of the other groups to each other. The database will be a common place for all of the teams to pull or store data as it is needed.

• The software being used is Apache Derby 10.11.1.1
• For a look at how we will implement see Database Access Object
• Examples of what the tables will be implemented see Database Table Implementation

Database Access Object

We will be implementing a data access layer that will handle the getting and setting to the database. This class will simply be created and called.

Example:

DatabaseAccess dba = new DatabaseAccess()
dba.SendPaymentToServer( float amount, String PaymentForm );

XML result will be similar to this:

<DatabaseAccess>
    <Payment>
        <Amount>32.00\</Amount>
        <Paymentform>Cash</Paymentform>
    </Payment>
</DatabaseAccess>

---

Database Table Implementation

NOTE All Dates are ISO 8601 Strings

Homeowner

• id : Int
• name : String
• email : String
• password : String
• memberSince : String

HomeownerPostedJobs

• userID : Int
• jobID : Int

HomeownerReviews

• userID : Int
• reviewID : Int

Contractor

• id : Int
• name : String
• email : String
• phone : String, max/min 10 chars
• website: String
• password : String
• finishedJobs : Int
• isPromoted : Boolean

ContractorReviews

• contractorID : Int
• reviewID : Int

Job

• id : Int
• budget : Float
• datePosted : String
• expirationDate : String
• description : String
• picture : String

• URL/Location? How to send multiple images?

• isFinished : Bool
• winningBid : Float
• hiredContractorID : Int

JobReviews

• jobID : Int
• reviewID : Int

JobBids

• bidID : Int
• jobID : Int

JobTags

• tagID : Int
• jobID : Int

Tag

• id : Int
• name : String

Bid

• id : Int
• contractorID : String
• jobID : String
• bidAmt : Float
• date : String

Review

• id : Int
• userID : Int
• contractorID : Int
• jobID : Int
• date : String
• comment : String
• reviewRating : Int ( Doesn't need to be a char, can be just 0 to 5 then handled by the GUI. )
• (0-5 stars?)

Push Notifications

• We will be implementing functionality of push notification. It will be implemented for:
• Direct messages from user-to-user/technical support-to-user (how messaging system will be implemented?)
• Notifying contractors when bid expiration is approaching (could be done for the day of an ending bid)
• Notifying winning contractor after bid ends
• Notifying user that bid has ended and who the winner of the job is
• Could be more uses but should be left up to discussion with other groups and what they plan on implementing

---

#Questions

• From Payment & Scheduling: Because we are having the homeowner pick a date for the job to be completed, and also choose a time from the group of times the contractor is available, we were wondering if you wanted to include a String for specific times in your list of items also to include with the appointment date and info?
• We will need / should get a list of what other groups will need from the database.This will make it much simpler for us in the creation of the database (hard to build something, and create a way for the other groups to get what they need, if we do not know what they need).

#Payment & Scheduling Requirements The Payment and Scheduling group needs the database team to have columns in the table to hold the following attributes:

1. Info for client:
   • Client Id/name
   • Available dates to let contractor work
   • amount of money in account
   • payment date
   • final schedule time
2. Info for contractor
   • Contractor Id
   • Amount for job
   • Amount of money in account (for adding funds)
   • Final schedule time

We will need the following info at the time of payment and scheduling:

1. Info for client:
   • Client id/name
   • Amount of money
2. Info for contractor
   • Contractor id
   • Amount of money in the account Note: we are not sure whether or not the information for the amount the job costs will be in the database at the time of scheduling. If it is we will need that as well.