Help creating database EAR model

[tomer999]

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):

http://img413.imageshack.us/img413/2471/tablesv.jpg

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,
Tomer.

[griswolf]

I've been working on a real world task similar. I arrived at something like this:

table customer (
  id integer,
  contact_info varchar, /*really several fields*/
  contact_flags boolean /* several (dis-)allow contact options */
  /* and anything else just about customers: email?, login?, hashed password? */
)
table performance (
  id integer,
  name varchar
/* location? time and date? box office contacts? */
)

table performance_seats (
  performance_id integer foreign key,
  seat integer, /* or varchar if you want 'row 22 seat 11' or foreign key if you want a lot of 'seat' info*/
  seat_type enum ('section-A', 'section-B', 'balcony')
  seat_cost decimal,
  customer_id integer foreign key, /* can be null: Not occupied */
  timestamp ticket_issued
)
/* performance_seats is almost 'ticket' */

In my case, I need to send invoices and offer refunds, so I needed

table payment (
  payer_id integer foreign key,
  paid_for integer foreign key,
  amount decimal,
  when_paid timestamp,
  when_refunded timestamp /* usually null */
)

Beware that I'm doing this from memory, making some translations from my nomenclature to yours, hiding some of my app's details and deliberately not trying to get the sql syntax exactly right.

[tomer999]

Hmm so you had the booking information in the Performance-Seat entity (you have the customer ID in there)? If I wanted to separate that out to a 'ticket' entity what affect would that have on the relationships?

[tomer999]

Oh and why did you choose not to have an independent seats table?

[griswolf]

You need to associate a customer, a performance and a seat. Ticket is just a piece of paper that the customer gives the usher to show the relationship. I don't see that it needs to have any presence in the database (we do have to have the association table, and as I said, it is nearly the same as a 'ticket') If you need to have an entity that seems exactly like a ticket, add an id column to the association table, and the ticket becomes a reference to that id's row in the association table.

If you have 'a lot' of information about a seat, then you want it in a table, and the customer-performance association table will make reference to the seat table also. That would be the normal form. But if the seat is a very simple thing, then you might be better off keeping all its data directly in the association table. You will still need to associate a performance with all its seats, either way. You do that, of course, by prepopulating the customer_performance association table with all NULL customers: One per possible seat.

In the real world, you would probably be a booking agent that has multiple performances in a variety of venues, and each venue would have information about its seats, and you would definitely want to keep venue and seat data away from the customer_performance association table. Your problem seemed not to need that. You should discuss this issue in your design document, no matter which way you choose.

Oh: I see that I named the association table badly. It should be named customer_performance. If the seat information is held in table seat, then it should be named customer_performance_seat (according to my naming scheme).

[asaukani]

usually the performance is not a single one, but it is done several times a day or several days so i think we need an extra table to performance-time an make relationship customer-(performance-time)-seat
for the seat, if there is several types of seat for examples stalls, balcony yes we need seat table unless just seat number is enough