Database Design - Orders

[redbrad0]

We currently have a system where users can purchase tickets to different events thru our website. I know that I am not doing things 100% correct so I am in the process of trying to come up with the best way to handle things. Current I have things very simple..

Orders - This table holds all the orders information
Orders_Tickets - Each ticket that is purchased is logged into this table and then referenced to the Orders table.

To try and be real brief so I do not take up much of your time the following things need to now be handled
1. Refund A Ticket - Either refund 1 ticket (incase they purchased 4 and only need 5) but we always keep the service fee per each ticket
2. Refund a entire order - Same as #1 where we will keep the service fee that was assigned to each ticket
3. When a event is over, we have to pay the venue/promoter. Sometimes we make multiple payments at different dates to the venue/promoter which all payments should be stored in the database.
4. Some venue/promoters only sell 5-10 tickets per a show, so we end up just paying them for a group of shows all at once.
5. Gift Cards - Not really where we mail out cards, but someone can send another user say $50 that gets applied to their account for Christmas or a Birthday. This user can then use the $50 to purchase tickets on our site, but if they do not spend the entire $50 on a event, it should take out for example a $15 order from the $50 which would make their balance $35 on the giftcard.

There is more to all of this, but I think if someone can give me some pointers that I could finish everything up.

[christo16]

Do you already have your two tables created?

[redbrad0]

Yes I have the two tables created, but I did not want to share the table names because I would rather someone give me their opinion if they were to write it from scratch so what I have done so far does not change their mind.

[Jenk]

table "orders" for order id, customer/user id, date of ordering and perhaps a text field for comments (by the operator handling the order) and even a second comments field which is viewable by the user ('Pending', 'Dispatched' etc)

table "order items" foreign key orders.order_id. product id, order id, quantity.


would be suitable for my simplest of implementations.