I had to write a work description for a potential Filemaker contractor and while doing that, I went over the database schema in detail. I found multiple problems to be fixed, some are already in other issues but I gather it all here as I go through the schema. These changes should be final unless we have a major blocking problem so if you need any change in the db, now is the time, please comment below. # Main modifications ## Fields * [x] remove artifacts.custom_designation * [x] artifacts.weight : decimal * [x] artifacts.thickness decimal * [x] artifacts.height decimal * [x] artifacts.width decimal * [x] add users:active bool * [x] add users:modified date * [x] add users:can_view_forum * [x] rename users:can_view_private_catalogues to users:can_view_private_artifacts * [x] rename users:can_view_private_transliterations to users:can_view_private_inscriptions * [x] rename users:can_edit_transliterations to users:can_edit_inscriptions * [x] rename users:can_view_IPadWeb to users:can_view_cdlitablet * [x] rename users:download_hd_images to users:can_download_hd_images * [x] rename users:filtering to users:hd_image_restrict_coll (int) -> use collection id * [x] add modified (date), email, and affiliation fields to the authors table * [x] create the articles table * [x] create the articles_authors table * [x] add sequence field to authors_publications and articles_authors * [x] add field "east_asian_order" in authors (bool) * [x] add inscriptions:structure * [x] add inscriptions:translation * [x] add inscriptions:transcription * [x] remove artifacts:modified_by * [x] remove artifacts:modified * [X] add artifacts_publications:publication_comments @larsgw * [x] remove artifacts:primary_publication_comments * [x] Convert artifacts:created_by to int (ref to author_id) * [x] Make sure all these artifacts fields are "bool" type is_school_text, is_public, is_atf_public, are_images_public, is_provenience_uncertain, is_period_uncertain, is_object_type_uncertain * [x] change to text type : stratigraphic_level, surface_preservation, general_comments * [x] remove artifacts:general_comments * [x] remove artifacts:dates_referenced * [x] remove artifacts:date_comments * [x] make artifacts:museum_no, condition_description, findspot_square, join_information, artifact_preservation, alternative_years, condition_description text type * [x] create new table "artifacts_sources" : id artifact_id author_id type (translation or atf) because there can be more than one author * [x] change name of credits table to artifacts_updates * [x] remove artifacts.credit_id field * [x] add a first_name and last_name fields to the authors table * [x] add last_login_time (date) and inactive (bool) to table users * [x] add retired_artifacts.retired_by int (fk = authors.id) * [x] add retired_artifacts.retired_for text * [x] add inscriptions.jtf text field (or json if that's possible, check FM compatibility) * [x] Make sure all tables and text fields are encoded to utf8mb4_unicode_ci * [x] Add a staff bool field to the authors table * [x] Add a relational table between articles and publications * [x] Create the highlights table * [x] Create a role table with roles and granular accesses in it * [x] Create a users_roles table * [x] add salt & iterations fields to the db * [ ] change all contemporary date fields to DATETIME format (YYYY-MM-DD HH:mm.. ) * [ ] Check that all FKs are set ## Changes in the data itself * [x] Add 6 highlights entry to the db * [x] Remove genre.genre id $ 8 after making sure any association from artifacts.genres with that genre id 8 is replaced by setting a 1 flag in the artifact.is_school_text field * [x] clay artifacts are marked as stone, change their material in artifacts_materials # Adapting the model, controller and view files * [ ] Modify all the models then views, test everything and correct problems... # Others * [x] Conceptualize how to manage retired entries so we can redirect from old P# to new P# * [ ] Conceptualize how to handle api suggestions and audit trail for artifacts catalogue changes #213