Best way to maintain secondary table

Waspman · September 14, 2021, 8:24am

Have a main “personaldata” table and a linked “medicalinfo” table.

Table structures are
personaldata: id, forename, surname, birthdate, gender, address… etc.
medicalinfo: id medinfo

Everyone has a personaldata record, but only a record in the medicalinfo if they have relevent data.

Everyone has the ability to update their own basic data using a HTML/PHP MySQLi form. Updating personaldata table is no problem, but am struggling with best way to update medicalinfo table.

What I have envisaged is checking to see if there is existing record, and if so delete it, then, if there is medical data entered create a new resord.

However it has been suggested that the code should check to see if there is an existing record then if no medical data in the form delete it, otherwise update it.

Which would be the most efficient way or is there a better way?

Strider64 · September 14, 2021, 10:32am

You could keep all the personal information and medical information separate with the id in the personal information database automatically generated (which 99 percent of the time it is). Then simply use the personal id to reference their medical information (if they have any).

Example: id would be their medical_id in the medical database table.

I learned a long time ago from being an actual patient is don’t delete the record (information) as the person might someday end-up calling the place. I actually had a medical office actually delete my medical history on me and I needed the information for the new doctor that I was seeing. It would be better to archive the data if you did anything. Though it sound more like you want to update the record which is OK, but even on my own online medical records that I have the data is simply added to my medical history.

Waspman · September 14, 2021, 7:58pm

Thanks for the reply - the situation you describe is what is actually happening. - The medicalinfo table only holds data if any has been entered.

The system is for a children’s sports club, and only medical info. that is relevent ito the child’s training is held. Sometimes parents change things, for example one year they might be recovering from a broken limb, but the following year are completely ok, so no longer relevent.

My question is really about the most efficient way to maintain the medicalinfo table.

phdr · September 14, 2021, 8:50pm

But it is relevant since the sports club is taking responsibility for a child’s well being while under their supervision. If the child had a reported past condition that isn’t taken into account at any point in time, the sports club can be held liable for any harm caused.

You would insert a new row with any newly submitted data and keep any old rows. Your medical info table should have columns for an - id (auto-increment primary index), person_id or a similar name (linking data back to the personaldata table), date (when the data was inserted), and the medical information. You would generally just retrieve the latest row (highest primary id for any person_id) that has a date corresponding to the club’s current season/session, but all the data is available if it ever needs to be reviewed.