All,
I currently have a database which manages files for different projects. For various reasons we start with a fresh back-end for each new project.
This is a slight over-simplification, but the gist is that information about different types of documents (by which I mean "what that document does" rather than "filetype") are stored in different tables. In theory, every type of document can be linked to every other type of document - these MTM relationships are done through junction tables -- at the moment, one for each conceivable type-type relationship.
That's all fine. Until you want to add a new document type, because you need to create a new junction table for every type of document.
I'd be interested in your thoughts on the wisdom of just having a single junction table for the entire database.
It seems to me it would massively simplify the architecture (in this case by reducing the number of junction tables by ~120), which would also simplify the code which adds records to the junction table (because there's no longer a need to determine which table to add the record to) and the number of queries required to find the joined records.
But it would mean either adding some unique table identifier to the Primary Key in each table (which is fine, though would mean you had alpha-numeric PKs), or adding two extra columns to the junction table to identify the origin of a given FK in that table.
The current way works fine, it's just a total pain to maintain...
I currently have a database which manages files for different projects. For various reasons we start with a fresh back-end for each new project.
This is a slight over-simplification, but the gist is that information about different types of documents (by which I mean "what that document does" rather than "filetype") are stored in different tables. In theory, every type of document can be linked to every other type of document - these MTM relationships are done through junction tables -- at the moment, one for each conceivable type-type relationship.
That's all fine. Until you want to add a new document type, because you need to create a new junction table for every type of document.
I'd be interested in your thoughts on the wisdom of just having a single junction table for the entire database.
It seems to me it would massively simplify the architecture (in this case by reducing the number of junction tables by ~120), which would also simplify the code which adds records to the junction table (because there's no longer a need to determine which table to add the record to) and the number of queries required to find the joined records.
But it would mean either adding some unique table identifier to the Primary Key in each table (which is fine, though would mean you had alpha-numeric PKs), or adding two extra columns to the junction table to identify the origin of a given FK in that table.
The current way works fine, it's just a total pain to maintain...