Edited and New Tables for the website

[michael-beaver]

The website is going to need some new tables and shift around the team table a bit to support new features that we want to have before MegaMiner. These changes should integrate a user + team system, instead of the teams just being synonymous with users, as well as adding a submissions metadata table that may have more added on later

From looking at previous commits I think I should hopefully be able to tackle this myself and put up a PR, but I still would appreciate some input on it, especially some of the data types

Worth noting, I don't think these changes should/could be pushed to chess.siggame.io. We could argue that the submissions_metadata could be put there (and maybe it should?) but I have a feeling that it will cause some unforeseen problems trying to set up the new table on the already existing database.

Existing tables

teams

Current Teams Table	New Teams Table
| Field | type| |---------|--------| | id | int4 | | name | varchar | | contact_email | varchar | | contact_name | varchar | | hash_iterations | int4 | | is_eligible | bool | | password | varchar | | role | text | | salt | varchar | | created_at | timestamp | | updated_at | timestamp |	| Field | type| |---------|--------| | id | int4 | | name | varchar | | is_eligible | bool | | is_paid | bool | | is_closed | bool | | team_captain_id | int4 | | created_at | timestamp | | updated_at | timestamp |

• Where team_captain is a foreign key to the users table.
• The teams table no longer needs any of the user specific information, and we decided that whoever creates the team will become team captain and will be the one who can send out invites to the team.
• I am not sure what the best type would be for the profile_pic column, any input?

New Tables:

users

Field	type
id	int4
name	varchar
contact_email	varchar
contact_name	varchar
hash_iterations	int4
password	varchar
salt	varchar
role	text
form_response	JSON
active	bool: default false
bio	varchar
profile_pic	bytea (?)
created_at	timestamp
updated_at	timestamp

• This is the new users table, the only new entry really is the form_response field, which we figured is where we would put the responses to the form that asks shirt size and pizza preferences, that way we don't need to make database changes if the questions change.
• Edit: I also added an 'active' field for email verification so they have to click on their email to activate their account

  teams_users

Field	type
id	int4
team_id	int4
user_id	int4
created_at	timestamp
updated_at	timestamp

• Where team_id and user_id are foreign keys to their respective tables. This is to reference which user is on which team

invites

Field	type
id	int4
team_id	int4
user_id	int4
is_completed	bool
created_at	timestamp
updated_at	timestamp

• These are where the invites will go, whenever someone invites another person onto their team it is stored in here, the is_completed field is to show whether or not the invitation has been accepted or rejected.

submissions_metadata

Field	type
id	int4
submission_id	int4
label	varchar
label_color	varchar
created_at	timestamp
updated_at	timestamp

• Where submission_id is a foreign key to the submissions table
• This table will be for meta data that users add on to their submissions. For now the only thing we have to add is a label and a color for the label, but there is room for expanding this later on.
• Is there a more appropriate type for the label_color ? It will likely just be a hex code, but after some research I didn't find a standard way to store that.

[user404d]

I'm fine with leaving this out of chess.siggame.io, too. These changes overall look good.

[user404d]

I'm pretty sure you meant int64 and not int4 for some of these right?

The profile pic could be the path on disk to the picture. If they are small, like 64x64, then you can just have them be in the db.

I personally don't think the form response stuff should be in the db. In my mind, it makes more sense to have that be handled separately from the rest of the web's responsibilities. As in have the users redirected to a google form or something of that nature. Having it be separate means it should also be something that someone running a competition opts in to doing. If you don't like the idea of depending on google forms, then develop a component that accomplishes a similar goal and store that data elsewhere. Again, I see this sort of thing falling outside of the scope of what is necessary during a competition.

A label should probably be varchar, the label_color could be a sequence of bytes for the rgb or whatever format you want to store it in.

[michael-beaver]

Duly noted on the label, and I just remembered that we needed an 'active' field for the users table for once we get email verification working, and I made those changes to the main post. I'll do some more looking later as to what to make the label_color

For the int4, that I just copied what I saw in the current database, for the implementation on colisee-lib I think it should just amount to making them integers in the same vein of:

table.integer("team_id")
                .unsigned()
                .references(`${TEAMS_TABLE}.id`);

I think you have a good point that we should just be using something like google forms for most of that stuff, and I think I would probably rather go that route for most everything; however, we were hoping that at some point in the future online payments will be figured out, and we were hoping that we could use that to 'secure' people's T-Shirts. Now I'm wondering maybe that is thinking too far ahead?

[user404d]

I'm not trying to completely shoot down the idea of managing registration information, but it's sort of an administrative thing that IMO should be an opt in feature. I don't think it should be the default. Like when deploying the website it would be nice to be able to turn that sort of thing on IF YOU NEED IT. Maybe I'm just stuck thinking that this stuff isn't relevant to the arena so it's not necessary (not a good thing on my part). What I'm thinking could work out well is having a separate db (or document store) which handles all of this metadata, but having a single db to manage also reduces management overhead so.

[user404d]

Oh and you're right about the int4; it refers to the number of bytes in the representation and not the number of bits so it's fine.