I just had the first session of an assessment this morning which I'll be doing over the next couple of weeks. I'm have some troubles though on deciding the architecture of the database; if anybody could provide some advice I'd be very grateful.
The scenario we've been provided runs somewhere along these lines:
Basically we've been asked to create a database with some forms and reports for a theater based on some raw data that we were given. The scenario states the system must be able to:
• show easily which seats are available
• book a seat for a specified performance
• add a new customer
• note whether customers want their details passed to the marketing department or not
• apply the appropriate discount when a ‘Friend’ makes a booking
• print the booked tickets for each performance.
I've also attached the full scenario if anyone is interested.
The raw data is held in three txt's with the headings "Customer", "Ticket" and "Seat". I've tried to remember all the fields (although I may have forgotten a few):
I've talked it over with a few classmates and the solutions we've chosen are all different, ranging between 4-6 tables with very different relationships. Whilst choosing the right model is only worth around 15% of the marks, it can cause significant problems later on if it is incorrect.
My initial reaction was to have five entities: "Customer", "Performance", "Seat", "SeatType" and "Ticket", although other people seemed to have also included a Bookings entity. I also haven't been able to come up with decent relationships (I'll post some of my ideas if people want).
Any advice would be really helpful and much appreciated. (Something like an EAR diagram would be fantastic but I know I may be asking for too much :P)
Anyway thanks in advance,