PREAMBLE: I'm taking a bit of a risk asking a slightly abstract question in a stack exchange (cough) such as this, but I'm hoping that someone may have come across this type of situation before. This is a question which is also likely to be useful to be others in the future. I can give detailed technological aspects in relation to this question, and the answer need not be "opinion" based. Furthermore, aspects in relation to this question are unlikely to change temporarily. So if you wish to downvote me because of my inexperience, or because you think you are smarter than me go ahead, but don't pretend it is because it is an inherently bad question. /PREAMBLE
I'm working with MySQL, and have developed a database to support a room allocation program. Aspects such as time, rooms, buildings are fixed and never change. However, the actual allocations entirely change every half year. As such the tables related to the allocation of users to rooms is entirely overhauled every 6 months. In order to preserve these records I duplicate the entire database, archive the old one and start off from scratch with the allocation information every time an overhaul is required.
I dislike the redundancy that is inherent to that process. The tables that never change need not be duplicated (and in my opinion, should not be duplicated). Instead it would make more sense for them to be shared between database children, which inherit this information.
Is this even possible with MySQL? No course that I have attended concerning rdmss have ever mentioned such a process. Looking on-line I have seen people state that the sharing of tables between databases would be a design flaw, but I do not see a more elegant solution. Are there better solutions to this design problem?
Rooms
table from the same database it's going to pull the rest of the data from. If there's a need to see all possible rooms, both current and historical, that's a somewhat different issue, and you'd need a table with both current and historical data, possibly one that knows when each item was applicable - which would be your table in another DB, available to all the historical archives, and the current active DB. – RDFozz Jul 24 '17 at 17:52