Gryph667
commented
2 years ago Proposed schema for new table. This would be a one to many relational set up to knight.pkey.
Table kdetail { pkey int [primary key, increment] fkeyknight int [ref: > knight.pkey] type varchar(20) [NOT NULL] detailname varchar(40) [NOT NULL] description varchar(100) [NOT NULL] detailalias varchar(5) crtsetid int [NOT NULL, ref: > knight.pkey, DEFAULT: 1] crtsetdt datetime [NOT NULL, DEFAULT: "CURRENT_TIMESTAMP"] lstmdby int [NOT NULL, ref: > knight.pkey, DEFAULT: 1] lstmdts datetime [NOT NULL, DEFAULT: "CURRENT_TIMESTAMP"] activeflg bit [NOT NULL, DEFAULT: 1] delflg bit [NOT NULL, DEFAULT: 0] }
Is your feature request related to a problem? Please describe. As a Knight admin, I need the system to keep track of all roles a Knight has on Discord, and potentially Reddit. If a user leaves, restoring their campaign badges is very difficult. Additionally, awards or titles granted by Grandmasters are not aging well, and are losing context. Storing this in Squire is vital to maintaining this data.
Describe the solution you'd like Two items are present, one is storage, the other is display and interaction.
Logically, all of the required information could be stored in one new table, using knight.pkey as a foreign key, and having columns for item name and description. Another column could reference image aliases if appropriate. The danger here is that some items could be entered multiple ways, affecting how they are displayed across the application. This could be solved at the interface layer, letting a user adding the record select from existing values (select unique style query), or add a new one.
Describe alternatives you've considered
Alternatively, awards could be stored and maintained on a discreet table like skills, and the associations written on a new "knightaward" table.
Using the skills table itself to store and assign the values would not work, as that would require non trivial redesign of how skills are managed by the users themselves.
Additional context N/A