How Should I Set This Up

PHP
1

Hi, I have been working on a site for a while now and I am completely stuck on a certain section.

The site allows parents to create small e-learning activities for young children, such as:

01 - pick out a certain color crayon from a bunch of crayons (1 additional step required - choose the color crayon that you want the child to pick out)
02 - pick out a certain animal from a bunch of animals (1 additional step required - choose the animal which you want the child to pick out)
03 - move the square object into the square hole (NO additional steps required)

  • many more…

If the parent chooses the first activity from the activity drop down menu when they are building the e-learning activity, they will then need to complete another step and enter in the color of the crayon… The same applies if they choose the second activity, as they will need to enter the animal name, HOWEVER, if the parent chooses the 3rd activity, no further steps / details are required.

I’m trying to figure out how to store the activities into the database and i am confused about how to do this as there are 3 groups of content which need to be saved in the database:

a - there are various different types of activities, which all have various different amounts of steps / settings required when building them which need to be displayed on the front end
b - then there is the chosen settings itself which have been applied / entered for each activity type (such as a chosen color or chosen animal) when an activity type has been chosen
c - and finally, there is the overall info itself that needs to be saved for each individual activity that is added to the site, such as child’s name and parents name which are associated with each activity that has been created.

I am very confused as to how I can store all of this info and link it all together in a database and also, how the front end will know how many settings need to be applied / displayed when a user selects an activity type from the drop down at the start.

Thanks for reading all of the above, and a bigger thanks if you can provide any help on this…

2

I would probably go in the direction of 3 tables to start. Those would be: activity, activity settings , activity global settings. The activity table would store all the individual activities. The activity settings table would store all the unique settings for an individual activity. The activity global settings table would store all the settings that are applied to each activity. So something like this to start:

activity

  • id (primary key)
  • title (human readable name of activity)
  • description (description of activity)

activity_setting

  • id (primary key)
  • activity_id (foreign key to activity that setting belongs to)
  • title (human readable name of setting)
  • required (boolean used to determine whether setting is required)
  • default_value (default value if applicable for unspecified setting)

activity_global_setting

  • id (primary key)
  • title (human readable name of global setting such as; parents name or child name)
  • required (boolean used to determine whether setting is required)
  • default_value (default value if applicable for unspecified setting)

In regards to the front end this would be enough to:

1.) create a list of activities for users to pick (create)
2.) Once an activity is chosen present user with configurable settings for the activity (global and instance specific)

Now once that is in place the question of how to store the data when a user creates an activity can resolved. In regards to that I would probably go with three three tables. Those tables being: activity_instance, activity_setting_instance, activity_global_setting_instance. The activity instance table would store any generic information for an individual activity that is shared by all activities. The activity_setting_instance table would store the specific configuration values for an activity created by a user. The activity_global_setting_instance table would store global configuration values for a single instance of an activity. With that the schema could look something like this:

activity_instance

  • id (primary key)
  • activity_id (foreign key to the activity that row represents)
  • user_id (foreign key to the user within the system that created the instance of the activity if applicable)

activity_setting_instance

  • id (primary key)
  • activity_instance_id (foreign key to activity_instance for which setting belongs)
  • activity_setting_id ( foreign key to activity_setting for which setting defines)
  • setting_value (the configurable value specified by the user for the setting)

activity_global_setting_instance

  • id (primary key)
  • activity_instance_id (foreign key to activity_instance for which global setting belongs)
  • activity_global_setting_id (foreign key to activity_global_setting for which setting defines)
  • setting_value (the configurable value specified by the user for the global setting)

Now one of issues that has not been addressed here is the representation of form fields for settings. In the case above it would be assumed that ALL settings instance specific or otherwise will be represented with a text field. Supporting selects, radios, check boxes opens up all whole other can of worms. Furthermore, there is no support for multiple values for a single setting. Lastly, there is no means of validating a single setting. Each on of those features mentioned would require more work and thought in regards to settings schema. So much so that while I did not do not use a parent child table schema for settings of global and instance specific it would be worth considering. Though depending on what global settings exist it may be best to make those columns in the activity_instance table. In theory the parent name and child name could be columns there.

Though that starts to bring up another point. While I have very little knowledge of the business goal of the application it seems like one would register than should specify their children. The “parent” would really be nothing more than the user and the child would than be a foreign key or maybe relation table depending on whether an individual activity can be assigned to more than a single child. These are all things that good to anticipate ahead of time considering they would drastically change the correct approach considering using “virtual” foreign key is bad practice. Though beyond that having a parent specific their children ahead of time would make it possible to use a foreign key a reduce replicate, none normalized data.

So if you were to have users (parents) specify children ahead of time or proceed with a look up upon activity creation for a parents children I would modify the schema as follows.

user_children

  • id (primary key)
  • user_id (foreign key to the user who represents the parent of the children)
  • first_name
  • last_name
  • middle_name

modify the activity_instance table as follows. Though the restriction here is that an activity instance can only have a single parent and child associated with it. If you needed to support assigning multiple children or parents a relation table would be needed instead.

activity_instance

  • id (primary key)
  • activity_id (foreign key to the activity that row represents)
  • user_id (foreign key to the user within the system that created the instance of the activity if applicable)
  • child_id (foreign key to child that activity belongs to)

I did leave some of details out here considering the schema designs shown here are really only meant to show relations and required data. The end tables may have other columns but the bare bones of a business logic are supported with what has been provided. Also, the data types have not been defined considering that is an implementation detail and everything provided is only meant to show a concept/rough of applicable tables.

A warning here I think is warranted. The business requirements seem to require getting really close to a meta relational data base. In that each activity has "unknown"settings which can be referred as attributes. The second you need to start supporting anything other than a simple text value for settings many bad things will probably start occur without rethinking / refactoring attributes into separate tables which can support other values such as; true integers, foreign keys, dates, etc. Storing everything as as text regardless of actual data type is really hacky though some what necessary when the settings are unknown or user is able to create them on the fly. It can become a delicate balance between practicality and idealism.

3

After thinking about it I would probably omit the activity_global_setting and activity_global_setting_instance tables. Instead any atomic data shared by activities would be appropriate as columns inside the activity_instance table. Any Activities that support default values could be specified as columns inside the activity table. Than any scalar settings could use a relation table if need be such as supporting multiple children being assigned to a single activity instance. That approach seems most appropriate considering the global settings will be known and cuts down replication of similar tables for different contexts without resorting to the polymorphic anti-pattern or parent-child table solution which has it’s down down falls.

4

Wow, thanks, i think i need to re-read your posts 10-15 times again but i understand where you are coming from, thanks for your help, it is much appreciated, cheers

5

Hi, another expert from another source, suggested the following… what is your opinion on this oddz:

Part of the reason why you find this confusing is because you’re trying to store stuff in the database that probably shouldn’t be stored there.

It is obviously necessary to store c in a database because this data is created by the program. However, a and b are both created by the programmer.

This is a nearly perfect use-case for object oriented programming. You have a base class “activity”, and then one extension class for each specific activity: “pick_crayons”, “pick_animal”, “move_square”, etc.

Your application will handle certain details that are necessary for all activities, such as collecting the parent and child names and choosing the activity. Then, it will use the code from the instance of the activity object to determine how many additional steps are needed and what those additional steps consist of.

You will need at least one table to store each created individual activity, which must have an ID and a reference to the activity type. Then you will likely want to have at least one table for each activity type that requires the collection of additional data. These tables might be things like “picked_colors”, or “picked_animals”. The table will vary based on what extra data that activity type requires. These rows will have a foreign key referencing the main activities table so that you know which extra data belongs to which individual activity.

There are many reasons to use an OOP approach to this rather than try to write some generic code and stick all of the logic into the database:

  • It’s a PITA the use version control on code that has logic stored in the database.
  • It’s a PITA to deploy to multiple development/staging/production environments.
  • Your database structure will be ugly, illogical and hard to query.
  • Your client will probably not understand the limitations of the generic system and features they want in the future may require difficult and widespread core changes because they were not designed into the generic logic during the design phase.
  • As a result, your application code will likely need lots of hacks and changes in order to support activities that require features that were not originally designed into the system.
  • Designing a UI for a form builder system is very hard to do well (what you’ve described is essentially a form builder).
  • Even if you do design a good UI, most users will never understand how to use it because they are not programmers.
  • It’s very hard for new programmers to understand existing generic code that stores its logic in the database.
6

My only rebuttal to that is whether users will be able to define new activities via an admin. If this is not the case than that approach would probably be less convoluted and easier to manage. However, if users need to be able to define new activities on the fly than the approach of having separate tables for each activity probably isn’t to viable.

7

Ok, i will only be adding 8 different types of activities to start with, and will not be adding new activity types on a daily or even monthly basis…

So do you think it would be best to create a separate table for each activity type, and then launch a specific set of code based on the the activity type that the parents selects at the start from the drop down menu…

8

Well so long as it is not a requirement that none developers be able to define new activity types using a separate table for each activity that extend a shared table (super-subtype pattern) that would be fine. In most cases it would be much easier to manage and less convoluted. My initial recommendation was in regards to none developers being able to define new activity types and configuration. If that is not a business requirement than everything can be much simpler. Though given 100, 500, etc activities could get out of hand… obviously but that seems to not be the case.

9

Ok cool, i think there will only 20 max and it will take probably a year to get to that… i really appreciate your help on this, thanks very much…