Multi-User ERD

[qube99]

I am learning a multi-user design.

 

I have 2 tables, Beasts and Items, that are in a many-to-many relationship using a join table. Each Beast and each Item has a flag that denotes whether the user has killed the beast or collected the item. All the flags are Yes and No.

 

The interface should be the user sees a layout of all Beasts with his user flags and a layout of all Items with his user flags.

 

Very simple ERD for a single user.

 

What's a good way to do this for multiple users?

[comment]

It sounds like you need to add a Users table and two join tables to your existing tables i.e.:

Beasts -< Kills >- Users -< Collections >- Items

It's not quite clear what is the relationship between Beasts and Items. Is there some connection between killing a beast and collecting an item (or vice versa)? The above assumes they are independent of each other.


It's not necessary to have both Yes and No flags. The absence of yes indicates a no.

[qube99]

The relationship between Beasts and Items is this...

A Beast may have a number of items. An item may be found on a number of beasts.

When we look at a Beast detail we see a portal that lists the items that can be found on that beast.

When we look at an Item detail we see a portal that lists the Beasts that may have the item.

The join table is very simple, it just holds the 2 keys for the beast + item pair. It currently has about 8,000 records in it that may grow to 10,000 as development proceeds.

 

The single user version is :

Beasts(flags) - Join - Items(flags)

 

I could add a user key and the 2 flag values to the join table. But this would mean duplicating the entire 10,000 join records for each user. We are planning on about 1,000 users so a join table like this might end up with perhaps 10,000,000 records in it. I am hoping for something a bit more efficient. Seems crazy to access a table that large every time someone changes a flag.

 

This multi-user version would be:

Beasts - Join(with Users + 2 flags) - Items

 

I have even thought about a join table for each user but that means up to 1,000 join tables and I'm not sure that's scriptable. At least the user would not be opening the entire record set to change a flag.

 

We don't anticipate more than 25 users at a time accessing this database. Normal daily traffic might be 100 visitors with a peak traffic of 200.

 

Another wild idea ... a multi-key field in both Beast and Item records that contains a line delimited list of all users? Sound nutty? Text fields are plenty big.

 

Your idea looks worth exploring. Can you please explain in more detail? If I can get my head around it I'll code it up and see how it does.

 

I've described a simplified version here that hilights my key questions. The final version will be more complex. A player will have a single account that contains 4 characters, each tracked separately and the entire account tracked in the aggregate. I think I can do the aggregate part with scripts once I have the individual part done. Collecting all the Items will require all 4 characters and even then there are a few unique Items that require purchasing from other players. Some items that are rewarded randomly from a list that will require characters to be deleted and recreated until the entire set is acquired. Yet other items require many thousands of hours to complete the required quests.

 

Also, the flags are more complicated than I describe but I can handle that if I can do a Yes-No flag in a 3-way relationship.

[comment]

LOL, it's amazing how in all of that you have somehow managed to avoid answering the most important question. Let me break it down to two:

1. When s user kills a beast - does he/she automatically collect all that beast's items?
2. Is there a way to collect an item other than killing the beast that owns it?

[qube99]

1. When s user kills a beast - does he/she automatically collect all that beast's items?

No

2. Is there a way to collect an item other than killing the beast that owns it?

Yes

 

The flags for Beasts and Items work independently of each other. You can collect an Item by trading for it with another player and never kill the Beast that drops it.  Items are dropped by Beasts according to a "drop table" where items have odds of being dropped. This means you can kill a Beast and no item at all will be dropped..

[comment]

Well, then my previous answer stands. Im not sure what additional details I can provide: it's two simple join tables. When a player (or a character?) kills a beast, you create a record in Kills, joining PlayerID to BeastID. Similarly, when a player collects an item, you create a record in Collections (perhaps Acquisitions would be a better name) joining the two.

 

 

Note that this is on the ERD level. You'll need more TOs for a practical implementation in RG.

[qube99]

This sounds like what I'd like to do. The part I have trouble seeing is how do I display all beasts using a join table that only has some of the beasts in it?

[comment]

how do I display all beasts using a join table that only has some of the beasts in it?

 

You don't. But you can use another TO of the Beasts table and cross-join it (i.e. using the x relational operator) to the Players table. Then you can use conditional formatting to indicate the status of each beast relative to the current player.

 

That's assuming you want to show them in a portal - because you could also show them in list view based on the Beast table; you just need to store the current player's ID in a global, so that each beast record can look into the join table and decide if it's alive or dead.

[qube99]

Actually I need to do both.

 

When viewing the Beast List I need a full lis of all Beasts.

 

When viewing an Item Detail I need a portal showing the Beasts related to that Item.

 

Now for the learning part. I think I can do the List View conditional. But I've never heard of cross-join or x relational operator. I don't think they were mentioned in either FTS 12 or in the Osborn Training videos I have. Sounds like something I'd really enjoy knowing. Can you recommend a source for details on this concept?

 

I'll PM you a link to an unlocked data entry version.

[comment]

I've never heard of cross-join or x relational operator.

 

Cross-join is the general term; x relational operator is the Filemaker term. You might have heard of it as "Cartesian product join" or the misnomer "Cartesian join".

 

 

 

I'll PM you a link to an unlocked data entry version.

 

I don't think you need to do that.

[qube99]

Thanks! That looks like just what's needed.