Finding overlapping data

[AndrewPurvis]

I have a database that stores the contents of—I'll just say it—Magic decks in such a way as each card-number-deck combination is its own record (this allows for other features not available in a single text field for each deck). My ultimate goal would be to have a means of comparing a given deck to all other decks for the number of cards in common, but this will always be less than 100%.

I have a Decks table, a Comments table, and a Contents table. The relevant fields are these, where "relevant" is interpreted loosely:

Decks::DeckName, Decks::VersionNumber, Comments::DeckName, Comments::VersionNumber, Contents::DeckName, Contents::VersionNumber, Contents::CardName, Contents::CardCount

In Decks, I can see, for a given DeckName and VersionNumber the contents of that version of a deck, and all notes on it (stored in Comments::DeckNotes). I can also view in a portal the list of all related records from Contents::CardCount and Contents::CardName (how many of which cards). I can even view individual card art via a popover button. This is great and lets me do about everything I want. Except one key thing.

I want to be able to see, when looking at a deck, all other decks in the database that have a minimum number of cards in common with the deck being viewed. I currently use a self-join on Contents::CardName to see other decks that have included that one card, but I want to be able to look at all cards from one deck to find, for instance, all decks with a >=60% cards in common. Can this be done?

***Update*** I would love to reply in thread, but there is no option available on the page for me to respond to the reply.

Edited August 1, 2015 by AndrewPurvis
Site bug

[comment]

I don't follow your description at all. I know very little about cards or magic. I presume that each deck can contain a number of cards, from a given list of cards? If that's so, then you should have a structure of:

Decks -< DeckCards >- Cards

The Cards table could possibly be replaced with a value list.

In some circumstances, the DeckCards  table could be replaced by a multi-valued field in the Decks table (e.g. a checkbox field listing the deck's cards). This would prevent you from recording anything about a specific card-to-deck join, as well reduce your reporting abilities - but it would have the advantage of keeping the composition of a deck in a stored field, which could then be used as a matchfield.

 

Once we settle the structure, we can turn to your question:

I want to be able to see, when looking at a deck, all other decks in the database that have a minimum number of cards in common with the deck being viewed.

That's not going to be easy any way you look at it. Let's have some more information first:

• Do all decks have the same number of cards? If yes, how many? If not, is there a minimum and/or a maximum?
• How many possible cards are there?
• How many decks do you have (or expect to have) in total?
• Approximately how many decks would there be in a portal that shows all decks that share at least one card with the current deck?

 

 

 

Edited August 2, 2015 by comment

[AndrewPurvis]

A deck may have any number of card equal to or greater than 60, though most are just 60. My personal collection is roughly 5,000 unique titles. The current deck count is in the hundreds, but not likely to reach 1,000 for years. The number of decks with common cards would reach into the dozens.

The only fields needed for the relationships are these:

Contents::CardCount

Contents::CardName

Contents::DeckName

Contents::DeckVersion

The DeckName/DeckVersion pairing is unique. A given CardName can exist only once in a DeckName/DeckVersion pairing, but there could be anywhere from 10 to 20-some unique CardName entries (each unique records) within a DeckName/DeckVersion pairing, and (generally) a CardCount between 1 and 4.

Here is a partial example of two decks, CardCount and CardName:

4 Champion's Drake

4 Coralhelm Commander

3 Hada Spy Patrol

3 Skywatcher Adept

4 Training Grounds

and...

2 Champion's Drake

4 Hada Spy Patrol

4 Skywatcher Adept

2 Training Grounds

3 Venerated Teacher

Other cards would be involved, but if these were it, I would be looking from the first deck at the second and see 10 cards in common (minimum of CardCount where CardName is the same), and that would be a 71.43% overlap. From the second deck's perspective, there is a 83.33% overlap.

The problem is that I need to find a way to compare things that do not necessarily have cards in common, then calculate the percentage of cards in common.

[comment]

I am afraid these numbers may be too high for portal filtering. It would probably be best to script this. Do these decks change, or do they remain constant once established?

 

 

Edited August 3, 2015 by comment

[AndrewPurvis]

I don't see how a portal could even get the right data because I cannot use a single relationship to test for all cards and return a result when some cards do not match. I use portal filtering successfully on numbers that can run into the hundred when specifying minimum games played, minimum wins, maximum losses, minimum winning percent, and minimum power rating (itself a complex, unstored calculation), so the numbers don't worry me.

The decks, once set, do not generally change, though there are sometimes tweaks in the last minutes after I import them from the design side.

I can see using a script for 1:1 deck comparisons that sets a fixed value, but I don't see how I can compare one deck to all other decks without cycling through each time.

The point is that years on I may create a deck that is 90% the same as a previous deck, which is what I want to identify without having to perform a manual search and review.

[comment]

so the numbers don't worry me.

Well they should, because when you have "dozens" of related decks, and each of these has 60 cards, you'd have to add up the results of 60 comparisons before you could pass/fail just one of them. And these comparisons would be rather complex too.

The main obstacle here is that each card has an amount associated with it. Ostensibly you could use FliterValues() to get the intersection and reduce the size from 60 to say 15. But then you'd have a problem locating the 15 corresponding amounts - on both sides of the comparison - among the 60 related records. It all adds up.

 

I can see using a script for 1:1 deck comparisons that sets a fixed value, but I don't see how I can compare one deck to all other decks without cycling through each time.

You start by placing the list of cards and a list of the corresponding amounts of the current deck into variables. Then you use Go to Related Records to create a found set of those "dozens" decks that have at least one card in common with the current deck. Preferably you'd open a new window for this, so that you can easily return to the original deck. Then you loop among the found decks, comparing their composition to the one stored in the two variables. If a deck passes, add its DeckID to a list in another variable. After the last deck, close the current window and set a text field in the original deck to the generated list of IDs.

[AndrewPurvis]

I stripped the immaterial bits out to get this to under 8MB from 1.58GB.

I realize I probably have a great deal of normalization to do, as I am not a professional developer, and this database has evolved rather like Windows: new things added on as the technology allowed, and the old never (or rarely) thrown away. Some of what remains in the relationship graph is extraneous for the purposes here, but the relevant fields are all in place. While the schema probably needs a ton of work, the underlying field definitions should be about as useful as they can be for this.

Deck Tester Copy.fmp12

Edited August 4, 2015 by AndrewPurvis
Updated file