Design assistance - mapping users to products

[Mike_Ball]

Hello

I'm looking for some assistance with my base design. I come from a SQLServer background and can do this easy here but not getting the best way to build for AT...

The Scenario
I have a list of users and a list of products.  It's collectibles so I want to be able to know what users have what products, and more importantly which don't they have.

In SQL I'd just create a UserProduct table and pretty much create a record for every user for every product and just record it there (not hugely scalable I know but not a big issue).

In AT I already have the two base tables Users and Products.  I know once I have the base data I can create Got/Not Got views easily but that base table is baffling me - probably over thinking!!!

I'm sure it's easy but I'm just not getting it

TIA
Mike

[TheTimeSavingCo]

Hmm, I'm not too sure I'm following so I put together something here.  What would be the next step from here?

[Mike_Ball]

Thanks for taking the time to answer Adam. I think it's on the right lines but unfortunately the link doesn't work. If you could check and I'll take a look

Really appreciate your help
Mike

[Blake_D]

If i understand what you are asking for correctly.

An option could be having 2 linked fields to products. The with and without. An automation can be created to look at a checkbox "Create Without List" where once you have the got products linked it searches for all not owned products and links them to that user which will help with the view. You can also go the route where if product link "got" is updated and the box is checked it updates the "Without" link to exclude that product. 

[TheTimeSavingCo]

Whoops, sorry about that, have updated the link!

[Mike_Ball]

Thanks Adam.

That's interesting. I wasn't aware of doing a "without" link.  How do you build that please?  Can't see the field definitions in your example.  I think that is the answer to my question - i want to know who hasn't got Product 1 so I can market it to them

Thanks for sparing the time to help
Mike

[TheTimeSavingCo · Accepted solution]

Hmm, after creating the records in Users and Products, I created a link between the two and manually linked them together?  So sorry, I think I don't understand what you're asking

===
re: Getting a list of who doesn't have Product 1

Ah yeah, you could use a filter for that and I've updated the base with a view called "No product 1":

===
The system above should solve your problem, but filtering like that also seems slightly tedious.  In these situations I also like to use a checkbox to filter stuff and I've created a view called "Easy product checking" for you to check out if you get bored.  When you mark a checkbox in the 'Products' table, this view in the 'Users' table will display users who don't have the marked product

[Mike_Ball]

Thanks Adam that makes a lot of sense, bit of lateral thinking compared to SQL I guess 🙂