How many tables?

[DarkHorseFarms]

Hi.

I am designing my first app. I am a equine sales agent and I am trying to manage all the info that I receive on a daily basis.

I have a list of horses available for sale and a list of people looking for horses. I need the system to match the available horses with the people looking by different criteria. I believe that I need to have four types of users - admin, trainers, clients and public.

So currently I have a table for the horses available, a table for the horse requests and a table for users.

I my industry many times I never actually deal with the person buying or selling the horse, but sometimes I do. Many times I deal with the trainer who is either selling the horse for the owner or buying a horse for the purchaser.

So I need to be able to link trainers to clients and clients to trainers, additionally I need to note which user owns the horse available and which user is the trainer. I also need to be able to track which user submits a request and who their trainer is. Sometimes a trainer will tell me what types of horses they are looking for... for multiple clients. When the trainer logs on I want them to be able to view all the matches for all of their clients. While when a client logs in I want them only to view matches for them not the other clients of their trainer.

My question is: Do I need to have a table for trainers and one for clients (owners/buyers)? Or can I use just one user table and still link the different types of users?

[MayMusic]

I believe you should create a user table with 3 yes/no fields:

UserTable

ID, username, pass, admin, Trainer, client

Then if there should be any relation then you can add another table to keep the relation. For instance:

Tranier_ID, Client_ID

These IDs are coming from ID of UserTable and shows which Trainer is related to which Client. Basically you are creating a one-to-many relation

[DarkHorseFarms]

OK.. I got the user table organized... now how to i set up the Trainer/Client Relationship table?

Most of the trainers will have multiple clients....

I will want the trainers to have the ability to add clients to their account and clients the ability to add trainers to their account.

The trainer or client being added will not need to be an active user... but I would like the ability to make them active.

I will want the trainer to be able to view all of their clients data and matching possibilities but I will want the clients to only be able to see info pertaining to them directly (and not be able to see all the trainers info).

[MayMusic]

How many trainers each client can have? Is it many to many meaning each trainer can have more than one client and each client can have more than one trainer?

It is better if you have two tables, client and trainers table.

If there is a many to many relation between tables you will need another table as well:

Client_Table:

Client_ID, Name, username, pass ...

Trainer_Table:

Trainer_ID, Name, username, pass ...

Client_Trainer:

Client_ID, Trainer_ID

If each client can only have one trainer:

Client_Table:

Client_ID, Trainer_ID, Name, username, pass...

Trainer_Table:

Trainer_ID, Name, username, pass..

Then you can create a report for each group trainer and client and have them login to the system. You should have different portals for them so you can create a DataPage and only include the fields they are authorized to see.

[Kayln123]

Hi

Once you have created a Table for each focus area in your database application, you can define relationships between your Tables.

Caspio supports both One-to-One and One-to-Many relationships and auto-detects the appropriate Relationship Type when you select your fields. When creating one-to-many relationships, you have the option of displaying values from the parent field in the related table’s drop down.

[DarkHorseFarms]

Ok.. I must still be missing something... my brain hurts

I now have the User Table which includes a checkbox for admin, trainer and client.

I also have a table User_Client and a table User_Trainer... decided to set it up as one to many... a client can only have one trainer where as a trainer can have many clients.

Now I am stuck on how to put it all together...

1. How do I get the info into the User_Clients Table and the User_trainer Table... then how do I link them together... ie specify which clients belong to which trainers?

2. How much info do i need in the User_Clients Table and the User_Trainer Table... especially if the info is already recorded in the User table...

i am assuming that I need to utilize the view feature but I am still a bit lost on how this all works.

[MayMusic]

If each client can only have one trainer then you need two tables:
 

 

Client_Table:
Client_ID, Trainer_ID, Name, username, pass...
Trainer_Table]:
Trainer_ID, Name, username, pass..

By entering Trainer_ID in Client_Table table you specify which trainer belongs to which client. To enter the value to the tables you can create a submission page: http://howto.caspio.com/videos/getting- ... apage.html

[NailDyanC]

Hi Just to update this post, here is the new and updated howto article on how to create a WebPage: https://howto.caspio.com/getting-started/web-form/