Q1. I honestly don’t think you need to change anything in your model. One to many relationships are fine to me.
Your model looks like it’s setup correctly already to me. I would just place the tables in different places.
Check out this video here for my recommendations on this.
Q2.
I think you need to decide first what type of cohort analysis you are completing. The one showcased here is time based.
But in your description you’re talking about something different. You’re actually describing grouping based on purchasing.
I understand these are very similar and in many ways there are a lot of similarities, but also a couple of differences.
All you need to do here is work out the start cohort, and I would place this column into the customer table, just like I have in the example.
From here though you then create the grouping based on purchases.
There’s no way to recognize time here though, as in when the purchases where made based on how you’ve set this up.
You need to choose. Do you want to group by purchases or do you want to group by when the purchases were made. I don’t believe you can have both.
Thanks
Sam