3

I want to design a vaccination chart for children.

Each child will have his own schedule and history of administered dates.

I am planning to store the administered dates of each vaccine right in the table of children, with each column storing the storing the administered dates of each vaccine. ( like child_chart(child id, vaccine1_administered_date, vaccine2_administered_date, ...)

Now if a new vaccine is found and to be added to chart, I need to make a new column dynamically.

Is it the only way or can I do it in any other way without dynamically creating a column.

This is my first project using databases. Please help me. Thank you

  • 1
    Adding columns and tables dynamically is almost always the wrong approach. Add some sample data to your question and you might get suggestions for a better approach. – Lennart Apr 1 '18 at 7:46
5

You have the wrong table structure planned.

Tip: Naming a series of columns on a table with a sequential number (foobar1, foobar2, foobar3) is often a sign of incorrect database design.

Many-To-Many

You have children and you have vaccines as entities. Each should be represented by a table.

Furthermore, you have a relationship between those two entities. Each child can get a dose of zero, one, or more vaccines. Each vaccine may be given to zero, one, or more children. That means these two entities have a Many-To-Many relationship.

A Many-To-Many relationship is always a problem. The solution is always a third table that bridges the other two tables, representing the relationship between them. The bridging table always has at least two fields; as a child table, it has a foreign key to each of the parent tables.

In your case, the bridging table would be named something like dose, tracking which kid got a dose of which vaccine. And in your case, the table would have a third field, the date when the dose was given.

ERD diagram showing patient-dose-vaccine tables

To present this data together, you perform a database join to extract various columns from the various tables where rows match up the foreign key on the child table (dosage) to the primary key of each the parent tables (patient & vaccine).

diagram of data-grid data drawn from the patient-dosage-vaccine tables

For more discussion of Many-To-Many, search for the canonical example: Books, Authors, and a bridging table named something like Authorship. Another example is employees being assigned to teams/projects. Yet another example is music albums where an album is categorized one or more genres, and each genre can have many albums assigned to it, as discussed in this Question.

Also search to learn more about how to separate such data into tables, a process known as database normalization.

Each child will have his own schedule and history of administered dates.

This Answer addresses only the “history of administered dates” portion of your problem, not the scheduling.

This is my first project using databases.

Your problem of vaccination records is not simple. As a beginner, I suggest working on a simpler problem.

  • 1
    Thank you sir, your idea of a bridging table really helped me get a solution. I now got a clear idea about designing tables for these kind of problems. But sadly, this project was assigned to me and there is no way for me to change my project. I should continue to seek help and practice more solving simpler ones. Thank you sir. – John Bonda Apr 2 '18 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.