Insert Registration Data in MySQL using PHP

Question

I may not be asking this in the best way possible but i will try my hardest. Thank you ahead of time for your help:

I am creating an enrollment website which allows an individual OR manager to enroll for medical testing services for professional athletes. I will NOT be using the site as a query DB which anybody can view information stored within the database. The information is instead simply stored, and passed along in a CSV format to our network provider so they can use as needed after the fact. There are two possible scenarios:

Scenario 1 - Individual Enrollment

If an individual athlete chooses to enroll him/herself, they enter their personal information, submit their payment information (credit/bank account) for processing, and their information is stored in an online database as Athlete1.

Scenario 2 - Manager Enrollment

If a manager chooses to enroll several athletes he manages/ promotes for, he enters his personal information, then enters the personal information for each athlete he wishes to pay for (name, address, ssn, dob, etc), then submits payment information for ALL athletes he is enrolling. This number can range from 1 single athlete, up to 20 athletes per single enrollment (he can return and complete a follow up enrollment for additional athletes).

---

Initially, I was building the database to house ALL information regardless of enrollment type in a single table which housed over 400 columns (think 20 athletes with over 10 fields per athlete such as name, dob, ssn, etc).

Now that I think about it more, I believe create multiple tables (manager(s), athlete(s)) may be a better idea here but still not quite sure how to go about it for the following very important reasons:

Issue 1

If I list the manager as the parent table, I am afraid the individual enrolling athlete will not show up in the primary table and will not be included in the overall registration file which needs to be sent on to the network providers.

Issue 2

All athletes being enrolled by a manager are being stored in SESSION as F1FirstName, F2FirstName where F1 and F2 relate to the id of the fighter. I am not sure technically speaking how to store multiple pieces of information within the same table under separate rows using PHP. For example, all athleteswill have a first name. The very basic theory of what i am trying to do is:

If number_of_athletes >1, store F1FirstName in row 1, column 1 of Table "Athletes"; store F1LastName in row 1, column 2 of Table "Athletes"; store F2FirstName in row 2, column 1 of Table "Athletes"; store F2LastName in row 2, column 2 of table "Athletes";

Does this make sense? I know this question is very long and probably difficult so i appreciate the guidance.

Answer 1

You should create two tables: managers and athletes

The athletes table would contain a column named manager_id which would contain the id of the manager who signed the athlete up or NULL if the athlete signed himself up.

During output, create two CSV files (one for each table).

Further reading:

Defining Relationships

Answer 2

If you will retain the names for a future submission, then you should use a different design. You should also consider if a manager can also be an athlete. With those points in mind, consider having three tables: PEOPLE, REGISTRATION and REGISTRATION_ATHLETE. PEOPLE contains all athletes and manager. REGISTRATION is the Master table that has all the information for a submission of one or more individuals for testing. REGISTRATION_ATHLETE has one row for every Athlete to be tested.

People table:
---------------
People_ID
Type (A for Athlete, M for Manager B for Both)
First Name
Last Name
Birthdate
other columns of value

Registration table:
-------------------
Registration_ID
Registration_Date
People_ID  (person requesting registration - Foreign Key to PEOPLE)
Payment columns....

Registration_Athlete table:
---------------------------
Registration_ID (Foreign Key to REGISTRATION)
People_ID      (Foreign Key to PEOPLE)

I am not a mysql person, but I would think this simple type of structure would work.

Finally, storing credit card information is problematic as it runs into PCI (Payment Card Institute) rules, which you will want to avoid (think complicated and expensive). Consider processing payments through a third party, such as Google Checkout, etc. and not capturing the credit card.

Answer 3

Well based on your comment reply and what you are looking for. You could do this.

---

Create one database for Registration.

Create the columns ID, name, regDate, isManager, ManagerID (Whatever Else you need).

When a Manager enrolls set isManager to 1 and form a hash based on name and regdate, that would be the Managers Unique ID that would be added to all of the Athletes entries that the manager registers.

When a lone athlete registers don't worry about the ID and just set isManager to 0.

---

I think I may be oversimplifying it though. Wouldn't be the greatest for forming different types of queries but it should be alright if you are trying to minimize your db footprint