DONATE:2012 Spring System Design Document

=Design Goals=

=System Architecture=

Current Software Architecture


The donate database manages information regarding donors and their donations to the YWCA and its various programs. The database includes the following tables:


 * donor: A donor can either be an individual or an organization. This makes the donor table the supertype of two subtypes, individual and organization. The primary key is donorID and is generated by the database server. The relationship between donor and either individual or organization is an inheritance relationship. Donor's attributes are street, city, stateName, zip, phone, fax, and email. That information is stored here because it pertains to both subtypes. Donor's attributes can take NULL values in case the donor is anonymous.


 * individual: An individual is a person who donates to the YWCA. The individual table is a subtype of donor. The attribute donorID is both primary key of this subtype table and foreign key that references the supertype. There are two additional attributes which are firstName and lastName.


 * organization: An organization is a company or other institution that donates to the YWCA. The organization table is a subtype of donor. The attribute donorID is both primary key of this subtype table and foreign key that references the supertype. Additional information about an organization is stored in the additional attributes: organizationName, lastNameContact, firstNameContact, and description.

The three tables described above have one-to-one relationships.


 * donation: A donation is made by a donor. There is a one-to-many relationship between these two tables. A donor can make many donations, but a donation can only be made by one donor. A donation can be either a monetary donation or an inkind donation. Therefore, donation is the supertype of monetary and inkind subtypes. The primary key is donationID and is generated by the database server. The relationship between donation and either monetary or inkind is an inheritance relationship. There are three relationships to other tables that require foreign keys. These tables are event, designation, and donor. An additional attribute, donationDate, records the date the donation was made.


 * monetary: A monetary donation is a donation with a payment of cash, credit card, check, or PayPal. The monetary table is a subtype of donation and is connected to donation by the foreign key donationID, which is also the primary key. This table is a child table of the payment table and thus has the paymentID foreign key.


 * payment: A payment shows what a monetary payment was paid with. It has a primary key of paymentID which is generated by the database server. The paymentType attribute holds the values cash, check, credit card, and PayPal.


 * inkind: An inkind donation is a donation that is not monetary, such as clothes, phones, etc. The inkind table is a subtype of donation and is connected to donation by the foreign key donationID. The description attribute has descriptive data about items that were donated.

donation, monetary and inkind have one-to-one, inheritance relationships.


 * event: An event is held by the YWCA to collect donations. This table has a one-to-many relationship with the donation table. An event can collect many donations, but a donation can only be linked to one event. The primary key is eventID and is generated by the database server. It also has the name attribute to specify the event.


 * designation: A designation specifies which program a donor chooses to donate to within the YWCA. A donor can contribute to the YWCA as a whole or to one of its specialized programs. This table has a one-to-many relationship with the donation table. A single donation can only have one designation, but a designation can be selected by many donations. The primary key is designationID and is generated by the database server. It also has the name attribute to specify the designation.

Proposed Software Architecture


The proposed database adds two tables to the existing database and makes some additional changes which are described below:


 * donation-line: A donation-line organizes donations. It has a one-to-many relationship with the donation table and is connected with a composite key which is donationID and lineNumber. A donation can itemize various contributions, such as cash value, or a check, or inkind items, each requiring its own line, but a donation line belongs to only one donation. The donation-line table becomes the supertype of 3 subtypes, monetary, inkind, and giftcard. The donationValue attribute holds the value of a donation for a specific line.


 * giftcard: A giftcard is an additional type of donation. It is a subtype of donation-line and is connected to this table by the composite key (donationID, line-number).


 * monetary: This table is now a subtype of donation-line and is connected to it by the composite key (donationID, line-number).


 * inkind: This table is now a subtype of donation-line and is connected to it by the composite key (donationID, line-number).

Parent Of Diagram


Parent of diagrams are important because they organize the order in which tables should be created and dropped. The diagram shown above is the parent of diagram for the proposed donation database. There are four layers, each showing the parents on top and the children below. Donor is the supertype of two subtypes, individual and organization. Therefore donor is the parent of both these tables. Event and designation are the parents of donation. Donation is the parent of donation-line. Donation-line is the supertype of three subtypes, monetary, inkind, and giftcard. This makes donation-line the parent of these three tables. Payment is the parent of monetary.

The sql code found below shows the order in which tables are dropped in the donate-create-tables.sql script. Children must be dropped before parents. The tables giftcard and donation-line have not been created yet. To understand the drop order found below look at the current software architecture diagram.

Help Subsystem
=System Services=

=System Infrastructure=