Hey there. As you can see I'm very new here Just been browsing and what not. I'm a first year Uni student and they've chucked us in the deep end with a tough assignment. We have to make a Conceptual ERD from this on Visio and then go to logical on ERWin. I'm pretty much sorted but I cant seem to get the payment/ticket/statement entities sorted out. Any help would be greatly appreciated. I have a few weeks b4 it's due but if I could get some ideas that would be really helpful (no I don't want you to do the whole assignment for me either, I just want some help on payment/statement, posted the whol assignment so you know whats going down )
Thanks for your time.
P.S. I'll post what I have done so far on Monday
The following information is planned to be stored in the database to be used by this system:
Host: a host is an organisation or club that chooses to advertise its events via the website. The information required about a host is: name, description, bank account, contact details.
Contact details for a host: first name and last name of the person contact, his/her email address, facsimile number, telephone numbers (business, home, mobile), address (first line of address, second line of address, city, postcode). A host could change its contact details over time. For example, a club host could use its secretary as contact. A secretary could be replaced at the Annual General Meeting of that club, or could change his/her address details during the year of office.
Stream: A host may hold a ‘stream’ of events over a period of time. For example, the IT Developers Club has a stream for eCommerce. This eCommerce stream holds eleven events over a year, each event addressing a different eCommerce topic. The information to be stored about a stream is: stream name, description. A host may be recorded in the database for some time prior to its requiring streams to be entered.
Event: Each stream belonging to a host may have none, one or many events. The information to be stored about an event is: event name, description, date, start time, finish time, venue name and address, parking details, presenter(s), charges for registration for the event in NZ dollars. Charges, once set, will not change for an event. Registrants will be charged according to the type of association s/he may have with the host. For example, students may receive a discounted charge. Association types will be described later in this narrative.
After each event has occurred, comments about that event are to be recorded in the database.
Presenter: Each event must have one or more presenters. The information to be stored about a presenter is: first name, last name, business title, short description of business activities, short description of background. Presenters are organised by the hosts and are not the responsibility of EventsOnline. The hosts will enter stream, event and presenter information into the database via the website.
Each host will also enter into the database the details of the associates to whom it wishes the website to email event advertising. The details of associates may be updated as often as once per month, depending upon the volatility of the host’s associations.
Associate: The details required about an associate are: If it is an organisation: name, email address of contact, type of association with the host. If the associate is a person, first name, last name, gender (required for gender specific presentations such as ‘Women in Leadership’), email address, type of association with the host.
Association type: There may be several types of association: some associates, such as committee members, may be able to attend events for free, some may be charged a regular member fee, students may receive a reduced fee, and some associates not deemed to be members of the Host club or organisation may be charged a ‘non-membership’ fee for registration in an event.
An associate can only have one type of association with one host, but that type of association could change over time. For example, a student may graduate and thus change from student to regular member. One associate could have associations with several hosts. Advertising for a host’s events will only be sent to that host’s associates.
Email advertising for events will include the web page address for recipients to click on to register for an event. There will also be the option, within the email, for the associate to choose to stop emails being received for that stream. This choice must be recorded in the database in order to prevent future advertising for that stream being sent to the associate.
Registration: Associates and non-associates may register online for events. The information to be recorded for a registration is: when registration is received, how the registrant chooses to pay. Registrants will have the choice of paying online using a credit card, or paying at the event. Payment by cheque will not be accepted.
To provide credit card privacy and security Gil and Albert have chosen to use WorldPay to collect the online payments, which may be made by credit card on the website. WorldPay (www.worldpay.com) was chosen because its systems were bank-endorsed and they were the longest established player in the eCommerce arena, being the first online shop with secure payments. Thus it is not necessary to store any credit card details in the database. If WorldPay accepts the payment, then the registrant is sent an email message, which is a numbered ticket for the event (see Figure 2). If WorldPay rejects the payment (say, if the credit card had been reported as stolen), then the prospective registrant is rejected.
After the event is held it must be possible to update the registration with the information as to whether the registrant attended.
If a non-associate registers for an event, that registrant’s details will be collected by the web site and added to the host’s associates in the database.
Weekly, WorldPay will place in EventsOnline’s bank account the total amount of the payments it collected during the previous week from the website, less the WorldPay commission which is currently 4.5%. It must be possible to store this information in the database in order to be able to compare with expected receipts from the registrations that were made during that same month. It is possible that WorldPay could change its commission percentage.
Statements: Monthly, EventsOnline will email statements to the host’s contacts, charging for the registrants recorded during the previous month for the host’s events that were advertised during that month. An event may be publicised over several months. Charges will remain constant for one event over the time it is advertised. A sample statement is shown in Figure 1. The host will be charged either the total EventsOnline fee for the number of registrations received that month, or a minimum charge, whichever is the larger. The statements will frequently be in credit. EventsOnline will transfer any monies owed to a host directly into the host’s bank account.
Payment: It must also be possible to record any payments received from hosts. A host may pay for several months on one payment – or may only pay for part of a month if the host is querying EventsOnline’s calculations. The amount paid, and when it was received, must be stored in the database. Hosts will only make payments if they receive a statement with a debit balance. A debit balance could occur if few registrants pay online.
- Have an address record for a host instance for each existing address line. Some hosts may only have one address line, some 2, or more... I suggest this approach b/c it is a rule in relational database theory that you want to prevent columns from containing a null value whenever possible. For example if you put Address1 and Address2 as separate columns in tblHost, then if some hosts have only one address line, then the Address2 column will be null for that row. You want to prevent this. Also, with the approach I suggest below, it allows the flexibility of adding an infinate amount of address lines (which is a good thing.) However, there comes a point when a database is too normalized. When database performance starts degrading because of too much normalization, then you denormalize one table at at time until you get the desired performance.
HostAddressID - PK
HostID - FK tblHost.HostID
Note: As part of your business requirements, you are only needing to store one physical address for each host instance, and the database structure reflects that. If you wanted to have multiple addresses for a host, then this structure would not work...
- Junction table (you may learn about how junction tables are used when you identify a many-to-many relationship)
StreamHostEventID - PK
StreamHostID - FK tblStreamHost.StreamHostID
StreamEventID - FK tblStreamEvents.StreamEventID
EventID - PK
HostID - FK tblHost.HostID
StreamHostEventID - FK tblStreamHostEventJX.StreamHostEventID
EventName - this may already be in tblStreamEvents.StreamEvent, as I am unclear about the business requirements...
EventDescription - this may need to be moved to tblStreamEvents
Address - may not want to normalize this like I suggested for the host address since I can see potential performance issues...
EventPresenterID - PK
PresenterID - FK tblPresenters.PresenterID
EventID - FK tblEvents.EventID
For the "Each host will also enter into the database..." business requirement, you probably want a few tables to store this data and associate the proper entities to track this information. I'm not sure how this relates to the other entities, but I'm sure you can figure this out.
tblAssociateIndivHosts - association with host
AssociateIndivHostID - PK
AssociateIndivID - FK tblAssociateIndiv.AssociateIndivID
HostID - FK tblHosts.HostID
For the "Email advertising for events ..." business requirement, you want to do the same type of thing as you will do for the "Each host will also enter into the database..." requirement.
Well...that's pretty much the core ERD for your business challenge, so I'll leave the rest of the fun for you. I hope this was helpful to you...it was pretty interesting to me to make this ERD. I wish you luck, and feel free to ask me any questions about this!
Hey thanks for that. I pretty much had that but it was good to sort that side of it out but yeah my main problem was working out the payment side of things but thats alright The normalisation ideas were great and I'll be working them in as I create my logical model. I'll let ya know how I get on
I dont think that tblStreamHostEventJX is needed since a stream can only have 1 host (i.e. not many to many).