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?
Best Answer
Normally when I have something like this, I have a table in one database and a VIEW pointing to that table in the other database. Can you just use a view on the other database which references the first one? If not then maybe a "materialized view/mirror table" of sorts which be a "slave" table that would get refreshed once an hour or once a day/week/month.