1

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?

Improve this question
8
  • Wouldn't it be easier to add the ability to control the status of the rooms so you can deactivate them when it changes? If you add in status, activate date, deactivate date and other data points like that you should be able to easily handle the changes that happen every 6 months without the need to create a new database or tables. – Joe W Jul 24 '17 at 15:35
  • 1
    In the short term, the tables may not have changed; however, in the long term, isn't it possible for there to be a change in rooms (through a building extension or some sort of renovation), or, for a new building to be added? How large are these tables, compared to the rest of the DB? I'd guess they're relatively small, in which case simply copying them from one instance to another helps future-proof things. – RDFozz Jul 24 '17 at 16:18
  • @JoeW I considered that, but the way I developed the database puts emphasis on being able to track many users' allocations across many rooms. A lot of the normalization is in place to ensure that someone cannot be in two places at once. Adding attributes to increase flexibility in relation to this doesn't really feel possible. – Stumbler Jul 24 '17 at 17:41
  • I am not sure why it wouldn't be possible, there just needs to be new checks to make sure that the room they want is not already deactive or not yet active and then the current checks that make sure you can't have multiple rooms at once should still work. – Joe W Jul 24 '17 at 17:43
  • 1
    If the frontend interacts with the archived data, I'd always have it use the 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
3

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.

Improve this answer
2
  • 1
    I'd recommend that the "common" table exist in one independent DB, with all the others (current and archives) accessing it via said view (or having a materialized copy of the view, if more applicable and possible). – RDFozz Jul 24 '17 at 17:53
  • Agreed! Great idea as it would be usable by another other database as well, not just these two. – MguerraTorres Jul 24 '17 at 17:57
1

Tables can be referred by full qualificators like common.table_a, aux.table_b etc from the working scheme main:

USE main;
SELECT *
  FROM table             AS w -- main.table referred
  JOIN common.table_a    AS z ON z.col_x = w.col_y
  LEFT JOIN aux.table_b  AS q ON q.col_m = z.col_n
 WHERE z.col_p = w.col_p
;

Sure client should have proper permissions for every scheme/table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.