Allow me to summarize. Our company provides an architecture design tool that, to simplify, allows customers to create projects, which contain architectures, which contain elements, interfaces, which contain requirements, tests, which contain.. etc etc. Lots and lots of nested relations. A project, after a few months usage, can easily contain thousands of records spanning two dozen tables, representing one-to-one, one-to-many and many-to-many relationships.
Currently all tables use an auto-increment numerical ids.. your standard stuff. Most tables also contain a project_id table which itself is just an integer.
Enter feature #1. The ability to export your project with all of its nested relations. This was simply enough.. we just load our project and recursively load all of its relationships, and then we just dump that whole thing as an array to a json file. This saves a full copy of the project with all of its relations represented by the values of thousands of primary keys at the time of export. Easy.
Enter feature #2. The ability to clone the above extremely complex project. Currently, we export the above, and then we have this complex script that iterates over every level of relation from the json dump and creates a new copy of everything. As it's doing this, it's storing all the reference primary ids from the json dump, and creating a relation map of sorts that it then uses to re-attach all the relations but using the new ids that are being created thanks to the auto-increment ids of the new copies.
Read that again if you must.. this one is a bit of a nightmare, this is not a piece of code I am happy with right now and is the constant source of issues. Before I describe what I think is a better way.. let's talk about the last feature.
Enter feature #3, the ability to restore a previous import (you can also think of these imports as a versioning system but not just for a single model but for a huge project). When you restore a project, you're overwriting the existing project and you must end up with the full working project with its thousand nested relations.
In a way, both cloning and restoring results in the same project state, but in the former case you end up with two projects where the cloned project will of likely diverge from the original, and in the case of restoring you still only have just the one project. I haven't done the restore feature yet.
Keeping in mind that the same database houses hundreds of these complex projects.. and each table having tens of thousands of records with different numerical ids that belong to different projects.
So restoring a project with the current system.. for example taking a project which at the time of export had 1000 nested records and now the current project has 3000, but after restoring we need to be back to 1000 (as an example).. this operation is quite a tricky one when using auto incrementing ids.
So voila.. this is the problem I'm currently facing. And I was just thinking aloud tonight of how to do this in a much better way.. and I'd love to hear all your thoughts about it before I talk about my new idea.
Really appreciate your thoughts actually : ) I don't come to Reddit often for coding problems but this one is a bigger task that's been on my mind a lot.
Oh and btw of course I'm using Laravel 10 here.
The first thought I had is that this could be dealt with with a NoSQL approach. But I kind of dread NoSQL and no way do i want to suggest that we move away from MySQL to something like Mongo at this time so we must stick to using a relational DB. I also don't want to use a different DB for each project. I am already dealing with 40+tables and having hundreds of dbs to manage would be a big nope.
The second thought I had is: use composite primary keys everywhere. Each project has a unique hash for a primary key and then everything else that is attached to it has a primary key that makes use of the project's unique key. Ignoring the pains and caveats that come with using composite primary keys.. this appears to make everything waaaaay easier. The export feature is the same as before. Cloning just involves creating a new project hash and then replacing it everywhere before inserting every records as is.. no need to relink things it just works and we just need to change one key.
And same for restoring.. we just wipe absolutely every record that contains the project key that's being restored and then we just restore the dump as-is.
Am I missing something or is this a waaay better solution?
The caveats with composite keys is that it can be less performant when making queries and it requires a lot of refactoring. Sometimes dependencies don't play well with composite keys.
I can't really think of another approach at the moment. What do you think about the composite key approach?
if you read this far.. I hope you're finding this mildly interesting! Thank you for making it this far and thanks a lot should you decide to share your wisdom developer to developer ;-)
Kind regards! submitted by /u/spar_x
[link] [comments]
0 comments:
Post a Comment
Thanks