How to model user data and relationships?

I wonder how to model something like this:

A user can create any amount of events.
Each event belongs to one user.

What I have done so far:
Created a new tables called 'users' and 'events'.
When a user signs up, a new entry in 'users' is created with its id from the built-in auth table. This column is called 'userId'.
However, when trying to set relationship to the 'events' table, the relationship is created via the 'user' table rowId. I cannot specify a key unfortunately.

How did you solve this?

Later, I want to do something in an admin dashboard: Get all events from user xyz

Hey! to solve that, you should create the record in events table with userId (created using Appwrite Auth only) i.e. while creating any record in events table, get the user's actual ID and then pass it as a value to the userId column

And in fact, don't create a separate users table at all as Appwrite Auth itself efficiently handles that

Until/unless you want to set some explicit attributes for the user

And if you really really want to create the users table, then also we've a solution. In that case, what you can do is link the userId column (not the default $id column, which currently you are doing) of users table with the userId column of events table

Hi @Devika ,

thank you for your feedback. I definitely need the 'users' table as I need to add additional porperties such as stripeCustomerId, subscriptionStatus etc.
However, I tried want you suggested but there is not way to expliclity choose userId as column for reference in the 'users' table

Ok. In the Column key (related table) try adding userId instead of users

In fact, added userId but the result is the same as I pointed out in the beginning

Ok. Can you share the code where you are handling the operations for these tables?

Sure.

const getEvents = createServerFn({ method: "GET" })
  .middleware([authAdminMiddleware])
  .handler(async ({ context }) => {
    try {
      const { session, user } = context;
      const { tablesDB } = createSessionClient(session);
      const events = await tablesDB.listRows<Events>({
        databaseId: serverConfig.appWrite.database.id,
        tableId: serverConfig.appWrite.database.eventsTableId,
        queries: [Query.equal("userId", user.$id)], # This query leads to an empty events array as userId != user.$id because userId is the row id 
      });
      debugLog("SERVER", "eventsService.tsx", "Fetched events: ", events);
      return events;
    } catch (error) {
      prodError("SERVER", "eventsService.tsx", "Error fetching events", error);
      throw Error("Failed to load events");
    }
  });

Ok. Can you also share how you're creating records in users table?

I think I found it 🙂

const createEvent = createServerFn({ method: "POST" })
  .middleware([authAdminMiddleware])
  .inputValidator(eventFormSchema)
  .handler(async ({ data, context }) => {
    devLog("SERVER", "eventsService.ts", `Creating event ${data.title}...`);
    const { session, user } = context;
    const { tablesDB } = createSessionClient(session);
    try {
      const event = await tablesDB.createRow<Events>({
        databaseId: serverConfig.appWrite.database.id,
        tableId: serverConfig.appWrite.database.eventsTableId,
        rowId: user.$id, // rowId needs to be set to user.$id, then it works 
        data: {
          title: data.title,
          description: data.description || "",
          startDate: data.startDate,
          endDate: data.endDate,

        },
      });
      throw redirect({
        to: "/admin",
      });
    } catch (error) {
      prodError("SERVER", "eventsService.ts", "Error creating event", error);
    }
  });

Is it working?

How the records are being stored?

Ah no, it is not working… row id must be unique for each entry in the table of course xD

I knew it. That's why I was asking for your users table code

Once we'll get the idea how records are being created in both tables, we can find a fix for the same

The code is essentially the same, but with without rowId and data has an additional field with userId: user.$id

I am really wondering why I cannot relate the users table with evens table on the attribute userId. Maybe I am thinking to strong sql-like with foreign key

You can do one thing, instead of manually creating the relation, you can first fetch the userId value from users table and then set it as value for userId column of events table while creating a record. Something like this (please adjust according to your code as I don't know what the rest of your code looks like) -

const createEvent = createServerFn({ method: "POST" })
  .middleware([authAdminMiddleware])
  .inputValidator(eventFormSchema)
  .handler(async ({ data, context }) => {
    const { session, user } = context;
    const { tablesDB } = createSessionClient(session);

    try {
      const users = await tablesDB.listRows({
        databaseId: serverConfig.appWrite.database.id,
        tableId: serverConfig.appWrite.database.usersTableId,
        queries: [Query.equal("userId", user.$id)]
      });

      if (users.total === 0) {
        throw new Error("User row not found");
      }

      const actualUserId = users.documents[0].userId;

      await tablesDB.createRow({
        databaseId: serverConfig.appWrite.database.id,
        tableId: serverConfig.appWrite.database.eventsTableId,
        data: {
          userId: actualUserId,
          title: data.title,
          description: data.description ?? "",
          startDate: data.startDate,
          endDate: data.endDate,
        }
      });

      return redirect({ to: "/admin" });

    } catch (error) {
      throw new Error("Failed to create event");
    }
  });

I see your point and will try it it out, i.e. I put this into my middleware function as well.
So as of now there is no way to define a custom column for setting up a relationship. It is always the built $id column, right?

Yes

Good 🙂
Thank you very much for your support!

Anytime! : )