Good afternoon,
I am beginning to scale out my database and I have the following problem, which I'm sure is common enough, but I suspect, having done some reading, that there a few possible solutions. Replication and XA transactions spring to mind, for example. Please bear with me. This is my first shot at this kind of problem. And please tell me whether I should redirect this question to another list. Here goes: I have a blogging system and want to separate the file functionality from the theme functionality. A theme is a skin for a blog, if you like, namely the combination of HTML, CSS, and files that constitute a particular blog's appearance. When designing themes you can bundle files with the theme, in the sense that they are associated with the theme and therefore exported alongside the HTML, CSS, and meta-data when the theme is exported. I therefore have the following tables. In fact this is a simplified view to capture the essentials: FILE ==== ID NAME DATA DELETED THEME ===== ID NAME ... DELETED FILE_THEME ========== FILE_ID THEME_ID FOREIGN KEY (FILE_ID) REFERENCES FILE(ID) FOREIGN KEY (THEME_ID) REFERENCES THEME(ID) Because the relationship between files and themes is many to many, I have an intermediate table. I have foreign keys to stop users deleting files when they're bundled with themes, bundling files with themes that have already been deleted, etc, etc. Now both the FILE and THEME tables have a DELETED column. Currently when these resources are deleted they are not really deleted, instead the current time is inserted into the DELETED column, rather than it having a NULL value, and the resources are removed about week later (0: this will allow me to implement a recycle bin at a later stage by disabling these delayed deletions for those prepared to pay for it :0) So in effect the foreign keys are already obsolete because I need to do something like this: ---------------------------------------------------------------------------- - START TRANSACTION INSERT INTO `FILE_THEME` (`FILE_ID`,`THEME_ID`) VALUES (1013,372); /* Check that the relevant file and theme haven't been deleted*/ SELECT FROM `FILE` WHERE `ID`=1013 AND `DELETED`=NULL SELECT FROM `THEME` WHERE `ID`=372 AND `DELETED`=NULL /* If either select returns an empty result then... */ ROLLBACK /* otherwise */ COMMIT ---------------------------------------------------------------------------- - This makes me think that I can easily move the FILE and THEME tables to different databases if I use a distributed transaction in the above. I understand that on a very large scale to insist on synchronicity. However, given that these kinds of operations are relatively rare, consider how often a user might upload a file, for example, compared with bundling it with a theme, I think this approach is acceptable. Apologies for the rather long email, but I thought it better to outline the problem in detail for the outset. Many thanks in advance for any help in this. Kind regards, James
