0

I am working on an application using the Yii framework, so its MVC-style. I have a record type "person", which contains information about membership. Members come in two types: adults & kids. With the kids, SOME of them we know their ages and they are divided into age groups. The age groups have names (e.g. "8 & Under".) So as an example, John Smith & Jane Smith have two kids Jimmy Smith and Janey Smith. Janey wants to do a soccer program, so we need to know her age and put her in an age group. Jimmy is doing arts & crafts so we don't need to know his age because his program isn't divided by age. And we don't ask for the ages of the adults.

So the question: I have a few choices about how I store this. I could:

  • put an int agegroup field in the person table, and define the values as CONST values when I define the model for people (and probably have a special value for "undefined" - for the adults. Then I need to write a little code to translate the const value to descriptive labels.
  • put an agegroup field in the person table as above, but also add another table in the database with the reference data. Enforce referential integrity to this new table. This raises another question - should I create a dummy record in the reference table for cases where I don't know/care about the agegroup, or should I allow null values in the people table agegroup field?
  • others?

I know I can make either of these work. But I would welcome advice on which approach is preferred and why!

johnmac
  • 166
  • 1
  • 7

1 Answers1

0

For the possibility of future expansion (you may want to create, delete, and modify age groups) I would recommend having an age group table consisting of a beginning age, an upper limit age, and a description. You can leave the beginning age as null to signify a 'up to n'/'n and lower' group, or vice versa with the upper limit. The primary key would consist of the beginning and upper age together (composite key.)

Then, in your 'activities' table, you would need to enforce a reference to the age group table (even if just to a 'null/null' or 'all ages' record.) This way you can create activities for age groups -- 8 and under soccer, 12 to 14 soccer, all ages crafts, 18 and older art, and so on.

tuespetre
  • 1,827
  • 2
  • 18
  • 30