DB design and implementation

[rprevost]

Hello to you all,
It has been a few years now since my last DB design. I feel quite rusty, especially with the numerous new features in AppGini. That is why I am submitting to you my new design for a SpeedDating Application (SDA). In order for you to understand, let me give you a summary of the functionalities I plan to provide with this tool. I want to manage Events where people participate. During the even, two individuals meet at a table and talk for a few minutes. After this time, one person leaves the table in order to meet another person. Between conversations, each participant has to "evaluate" the person she or he just met by completing a scorecard. With 20 persons (10 women and 10 men), 100 scorecards will be produced with 10 scorecards per participant.
Features:
People will sign-in with username (pseudo) and password. Admin will manage submitted registration.

Members Table > Once a user is accepted, Admin will create a new member with relevant information (pseudo, date of birth, gender, email, etc.) Some fields will be taken from the memberships internal table. Members will be categorized by age and by gender.

Events Table > A schedule event will be held on a given date and time at a given location. Admin will manage this Table
Locations Table > List of place where events will take place. Admin will manage this Table

Event_Members Table > Intermediate table for many-to-many relation. Managed by Admin.

Scorecards Table > Each participant will enter, one at the time, the evaluation of the person he or she just met.

Scorecards data will be compiled in order to find matches between compatible couples based on their willingness to pursuit their exchange.
Scorecards data will be exported as csv and will be analyze the following day in order to provide each participant will an overall appreciation of his or her performance and subjectively reported by the people he or she met.

SpeedDating.png (51.29 KiB) Viewed 266 times

My questions are:
Does the DB design make sense? What other ways might be more appropriate?
For the scorecards Table
How can the username be retrieve by default? - "creatorUserName" ??
How can I make sure that for a given event the right event_id will be automatically entered (read only) in order to minimize error?
Will the one to many relation between participant_id (the person evaluated) by evaluator_id and the event_members.member_id will be sufficient in order to display only the people present at the given event?
In order to be able to list all the scorecards created for a given member without taking account of events, should I had another relation one to many between members.id and scorecards.participant_id?
Do I need to add more lookup fields in the event_numbers Table?
event_members.event_id already retrieves the date and location of event
event_members.member_id already retrieves a calculated field (age-gender-pseudo) of the participant
Which new features, if any, brought to AG in the last 3 years might be relevant to this project?

I know this is a tall order for me. I hope with your precious help I might be able to pull this one out.
Regards and thank you for you patience.

[onoehring]

Hi,

interesting.

How can the username be retrieve by default? - "creatorUserName" ??

Should be correct. You can choose this default from the AG setting.

ag_creator.png (12.45 KiB) Viewed 46 times

How can I make sure that for a given event the right event_id will be automatically entered (read only) in order to minimize error?

I suggest to make this a read-only and hidden field and leave it empty. Then, after the user submits the form, add the correct ID (that one, that matches the current date ... as you have the event date in the table) by code.

Will the one to many relation between participant_id (the person evaluated) by evaluator_id and the event_members.member_id will be sufficient in order to display only the people present at the given event?

Yes, I believe so, just try. Maybe you need a dependent lookup.
Or: create a custom SQL for the dropdown that selects people that are part of the current event (on the current date / even better: those having the event ID in the event_members table. Try excluding the actual person having the application open and being logged in, so noone can choose to evaluate herself.

In order to be able to list all the scorecards created for a given member without taking account of events, should I had another relation one to many between members.id and scorecards.participant_id?

I think no: Instead, you can create a view in the database, let's name it v_evaluations for all evaluations of and for all members. This view is a SQL statement that collects all data joining members, event_members and locations tables by the members.id field. Then, create a new table in AG which has the same name and fields as your view (e.g. v_evaluations) to have a general AG browser page. In that page the admin can filter by member ID in the evaluator_id and the participant_id fields. I suppose, this should give you the desired "all scorecards by person" "all scorecards for person" lists.
Btw. Your fieldnames do not make understanding easy (for me)

Do I need to add more lookup fields in the event_numbers Table?

There is no table with this name

event_members.event_id already retrieves the date and location of event

IMHO not a good thing for primary key. Use autonumber instead - or do I have a misunderstanding?

event_members.member_id already retrieves a calculated field (age-gender-pseudo) of the participant

IMHO this is not a good pseudo. Why not simply take the primary key?

Which new features, if any, brought to AG in the last 3 years might be relevant to this project?

Calendar?

PS: You may want to add a descriptive field to the events table: A name or something probably.

Olaf