Hi,

Our current ticketing system contains a great deal of its business logic in the application code. I have to rebuild the ticketing system, and I'd like to put as much as I can of the business logic into the data structure. However, I'm having real problems conceptualising the entities involved, and figuring out how to model their relationships within the database.

I've attached a diagram of my work-in-progress. An overview of the business rules I'm trying to mirror in this schema:

A ticket, at its most basic, consists of a price record and an availability record for a combination of three parameters: an event, a stand and an admission date. The admission date may be a single date or a group of consecutive dates. A grandstand is also tied to an event (each event has its own grandstands), although I'm not sure if that's a rule I need to enforce?

The price and availability of a ticket then varies according to any combination of the following:

- When the ticket is booked
- The ticket's class (I've called it Concessions in the diagram attached - basically it's Adult, Child or OAP tickets)
- The quantity booked (ie: discounts for bulk orders)
- The user's membership type (if any) - membership may allow you to purchase tickets in member-only grandstands, or entitle you to a discount.


Those are the simple ones. Stuff that I'm having real problems conceptualising includes:

Package tickets - I'm assuming that I can just add a "tblPackages" table that allows tickets to be grouped, and assigned a unique group price.

Complimentary tickets - when you book as a particular member, or book a certain quantity of tickets for a particular stand at a particular event, you may be entitled to free tickets that should be automatically added to your basket. I'm completely stumped as to how to manage this in the database - it sounds to me like something I'm going to need to manage in the code.

I... errr.. haven't asked a question, have I? I'm sorry, I guess this post is more of a brainstorm for me than a request for specific help; I find it easier to get my head round stuff when I've got to explain it as clearly as I can to someone else. But... well, if you do look over the attached diagram, and anything I've said above does stand out as a glaring anomaly, then I'd very much appreciate your comments.