正在加入特定类别或票证的竞赛

Question

I have now a PHP/MySQL website for signup and payment for running races.

My main problem (which blocks me now at all) is, an user sign up, and pay inscription for a single race. This single race has MySQL field "price". running race <-> single price.

In a month I will have a new event, and the organizer wants multiple prices for different runners (depending age, child, senior, etc)

My actual MySQL Schema is

USERS (**id**, username, password, email)
USER_PROFILES (user_id, name, surname, gender, birthdate, etc...)
EVENTS (**id**, name_desc, date, price, ...)
REGISTRATIONS (id, user_id, event_id, signup_date, bank_order, bank_result)

I imagine the user website flow, but I don't know how to implement it in DB. The flow would be: - Types the spanish ID card. - The system check if it's joined previously. - If not, the user fills personal data form. - (New) Click the category/ticket/price for this user. - Adds the user to a "cart". - Pay all runners in single payment.

---

Ex: http://www.cronometrajes.com/en/guest_signup/race/ii_carrera_popular_olleros_de_sabero

Test ID: 71000000N

Spanish phone format: [0-9]{9} Ex: 666554433

---

Due to my website schema, I don't know how to improve my actual site with this other feature.

I hope I have explained everything perfect. Sorry about my bad English.

Answer 1

This is simple and you are almost there already. You know that you need multiple prices per event. Which means you need some sort of event price list object that relates to an event. Then you can have many prices per event.

So now you could create something like

event_price_list (id, event_id, max_age, price)

So this data would look like

id, event_id, max_age, price
1,  1,        10,        80
2,  1,        18,        100
3,  1,        21,        140 
4,  1,        60,        60

Now you can use the event table to get all event price lists and using age you could work out which price list you want. You could potentially create another table called age_brackets or something like that which would relate child, adult, senior to actual ages if you want and this could be referenced in your price list table or you could manage that in code. There are a couple of variations, but this should get you started.