Relating Tables with no previous Relation (SQL Server)

EDIT: I swapped out my structural implementation, but my problem still remains the same; i.e. First creating a Publication object (if it doesn't yet ex开发者_运维技巧ist) before creating the SvcRequest record.

I was given a database that I had to "fix" and am quite perplexed about a particular problem. To keep it simple, we have two tables (of consequence) that already existed. It took me awhile to figure out the relationship between the two and ultimately decided on a traditional junction table as follows:

                     Publications (id, SvcReqID, LogID)
                             /              \
                            /                \
               SvcRequest (id)              SvcProgressLog (id)

SvcRequest & SvcProgressLog are more or less sibling tables both containing a reference to the parent. There is this weird hierarchical kind of relationship that took me awhile to figure out, now I just need a way to join them which makes it easy to perform CRUD operations.

The process here is as follows:

1. A service request for a publication comes in.
2. If the Publication exists --> update the corresponding Publication record. If not, create a new Publication. After that, create the SvcRequest record from information captured from a webform. (help needed here)
3. Finally a Log entry can be created for Requests that exists, but not yet Logged.

The following relations exist:

• Publications --> SvcRequest :: 1 --> Many
• Publications --> SvcProgressLog :: 1 --> Many
• SvcRequest --> SvcProgressLog :: Many --> Many (-ish)

As always, I greatly appreciate the help and words of wisdom ;) よろしく

Because of the complexity of our database structure and all the supporting tables for what I have called the SvcRequest table here, I have opted to write a series of stored procedures that can be run individually to "update" (though actually an insert) requests or called by a master SP for entering new entries.

After hours of thought and design trials, this is the best solution I can come up with. I'll post some sample code after it is written so that others may benefit as well. :)

If the link_id cannot be used to relate the SvcRequest and SvcProgressLog tables then you will need to disassociate the SvcProgressLog from the Publication table and relate it to SvcRequest directly instead:

Publications >-----+ SvcRequest +-----< SvcProgressLog