Relating Tables with no previous Relation (SQL Server)

Question

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 exist) 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:

A service request for a publication comes in.
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)
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 ;) よろしく

Can you tell from the incoming service request whether the publication exists? — Mike Sherrill 'Cat Recall', Sep 14 '11 at 13:46
@Catcall: Unfortunately no, it must first be checked against the publications table. — Chiramisu, Sep 14 '11 at 16:07

score 1 · Accepted Answer · answered Sep 14 '11 at 19:25

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. :)

score 0 · Answer 2 · answered Sep 13 '11 at 18:31

0

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

answered Sep 13 '11 at 18:31

Wil

4,130
1
16
15

I'm having a hard time making sense of your comment. :-/ – Chiramisu Sep 13 '11 at 19:37
Publications has a one to many relationship to SvcRequest. SvcRequest has a one to many relationship to SvcProgressLog. – Wil Sep 13 '11 at 19:54
Not quite, SvcRequest actually has a many to many relationship to SvcProgressLog. Hence the need for the "junction" table between them. – Chiramisu Sep 13 '11 at 21:28
So is Publications the junction table? Or is it a separate entity entirely? If the latter, you need a second junction table between SvcRequest and SvcProgressLog. – Wil Sep 13 '11 at 22:16
It is the essentially the junction table, yes. But it also has a few of its own fields. – Chiramisu Sep 14 '11 at 00:59
Can you post a sample of the three tables... Script the tables out with some sample data. There are numerous ways to perform the operations to get the data in the tables in the right order. – Wil Sep 14 '11 at 03:10
I appreciate you're sticking with me. I have changed my approach a bit to account for the complex structure of our database and business needs. Thanks ;) – Chiramisu Sep 14 '11 at 19:30

Relating Tables with no previous Relation (SQL Server)

2 Answers2