David Lewis
DavidL at sierranevada.com
Fri Nov 7 12:14:48 CST 2003
Hi all, I have a db redesign issue facing me and I'd like some advice: The database is for a brewery, and has been in use for a number of years (my design, and as it is it works fine). A broad brush overview of the structure is Brewhouse>Linking Table>Fermentation>LinkingTable> Filtration>Linking Table>Packaging>Linking Table> Shipments The linking tables between each department enable many to many relationships. Now, the fermentation department has found it necessary to do their work in a slightly different way for a few types of beer, which this schema does not allow for. In essence they will be consolidating a number of tanks into another (this will also be a many to many relationship), for some extended lagering before filtering. I think this could be accomplished with a self-join on the fermentation table, but I am not sure -- I have no experience using these in a db design. The db is not small -- hundreds of tables, many times more views, sprocs, functions etc. My concern is what effect this kind of design change might have on things upstream and downstream -- on all the reports and such that are built on the 'old' schema. I haven't thought through the self-join idea thoroughly, but I assume that all tanks will join on themselves by default, unless they undergo this new treatment, in which case they join on the new tank they are transferred into. Is this correct? In this case the correct joining field for all my queries and reports and such will need to be the newly created key field, correct? If anyone has any comments of any kind I'd appreciate it. Also, alternative ways to solve the problem are welcome. TIA. D. Lewis