Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2008-03-28 : 07:47:48
|
| Hello,I am relatively new to database design but think I understand most of the basics but would appreciate some clarification on a few points. I have just been lumbered with what I believe an extremely inefficient and badly designed database and would like to improve in the most logical way.-How should many-to-many joins be structured and queried?The database I'm dealing with at the moment has a table for water pipelines which can connect up to a number of different objects that exist in other tables, for instance to a house, a water storage facility, a water pump or even to another pipe. To 'connect' to these other objects, the 'pipeline' table has 'ToId' and 'FromId' fields, which contain the primary Ids of the object that is connected to the pipeline. This means every object needs to have a unique Id from every other object, regardless of whether they are stored in different tables, which seems less than ideal. Also, to connect two connected piplelines end having foriegn keys that are also primary keys in the same table, which seems a little strange.I would guess a more suitable solution would be to have junction tables for each type of connection, I.e. a table for HouseToPipeline connections, a table for PipelineToPipeline connections etc. Then the question becomes what to use as a primary key? I have read about composite keys, and these would seem idea in this case, but also read that these can cause problems and am a little unsure how to progress.A more subtle problem is how can I query these kind of connections? For instance, a common query might be to find out what water pump a house is ultimately connected to. I would be a bit confused how to go about this without making lots of joins, and PipelineToPipeline connections could makes this extremely difficult, as there could be several of these in a row. I can imagine some kind of recursive query that would stop when it got to a PipelinePump connection, but it seems a bit complicated. Maybe this is because I do not have the right idea for the structure.I really appriciate the effort you guys put into answering all the questions posted here, thanks! My other questions are in different posts so that answers are only related to one subject. |
|
|
|
|
|
|
|
Mutiple Many-to-Many Joins