[dba-SQLServer]Self Linking, mid stream db redesign

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


More information about the dba-SQLServer mailing list