I am designing a world-building app which allows the user to swap out project files (e.g. Word and it’s docx files), however they will also be able to save data that any project can access (think Word’s auto correct or dictionary).
The problem is both kinds of data need to be stored in the same tables. For example, in the app, the user can open a specific form and add a subject, let’s say Maths. To them this is added to the “Library” (to be accessed by any project) however in the database this subject is simply added to the tbl_subjects table, and be given a value of 1 in the ‘_type’ column to indicate that it’s a Library record. Likewise, the user may also add a subject under another specific type of form which only adds subjects related to that particular Project’s fictional world, like Demonology. Again, this record is then saved to the same tbl_subjects table and given a value of 2 to it’s _type column. Why store both together? Because a table such as a junction table tblJ_school_subjects needs its subjects FK to refer to both kinds of records. On the app itself, the user can select from all subjects in a dropdown list with Maths and Demonology being sorted a-z together.
The issue of course is that, the records with Type 2 need to be replaced when a new project is loaded. This means everytime the same project is re-opened, the records re-added through code may have a different ID (for example there may have been 5 new records of _type 1 added since the project was last opened). This will mess up the other tables that rely on these as foreign keys. For example, let’s say, the user created a school with Project 1 and the school_subjects junction table’s first record has a FK value of 12 because it wants to refer to Demonology (and Demonology was the 12th entry in the subjects table). That’s fine but fast forward a few days and the user has since closed Project 1 (deleting the Type 2 records) opened Project 2 (generating its Type 2 records from the project text file) and with this project open they also added 20 new Library entries. Later, Project 1 is now re-opened and through code parsing P1’s text file, the record of Demonology is added back in, however this time it has a value of 32. The project’s school_subject junction table’s single record of 12 is now inaccurate.
My first idea was to use a date (to the second) as every record’s ID but this feels clunky and expensive.
My other idea is the question i’m asking here. Can I create a composite Primary Key with two sets of auto-incrementing values? So Type 1 and Type 2 have separate counters? E.g - 1-1 - 1-2 - 1-3 - 2-1 (first project record) - 1-4 - 2-2
This way I don’t have to be concerned about how many Library files are added.
I don’t want to store the project data in one database because I want the user to have a similar experience to writing / document apps and have the ability to move, duplicate and backup individual project files as they see fit.
I hope this makes sense and thank you for any help
