Dates and links

[Anonymous]

I have 4 tables, a users attendance which contains there attendance for an idividual date, a users table, a groups table which organises users into group, the can move between thoose during the year and a link table.

Trying to create a slicer, which will allow me to filter the attendance date to see attendance during a certain peroid of time and also filter the groups table, to give me context for group attendance on a particular day, depening if a user moves groups.

I tried creating a new field combining the attendance date (converted to format 20181019) and the User ID (which oriognally comes form Mongo so is a string, seporated by an _ e.g.20181019_abc123abc456789) to create a unqiue field to then match in this table, then created the users groups table by creating an entry for each day between the start and end date for the student, then creating the same unique ID as above to match.

This works but as the attendance table is around 25 million rows and the users groups table is around 300k to start, but changing as above increases the users groups table to around 76 million rows, which introduces a very high cardinality.

Is there a better way to achieved this? Given there must be a link between date and the student to the attendance table?

My structure is as follows:

Attendance Table

• Attendance ID
• User ID
• Attendance Date
• Present (Boolean)

Users table

• User ID
• User Name

Groups Table

• Group ID
• Group Name

Users Groups Table

• User ID
• Groups ID
• Start Date
• End Date

[kentyler]

You might consider combing users / groups /user groups tables into one table that has one record for each day each user is in a specific group.

That would have the same "day" granularity as your attendance table and would make it easy to get the group attendance numbers for any given day.