| Author |
Topic |
|
lynnbt
Starting Member
9 Posts |
 Posted - 2002-07-30 : 19:08:33
|
| ... to syncronize data between 2 tables? Let me give an example:I have a database that maintains data for multiple organizations. I want to give a user the ability to create an org structure for each org. Here is my org table:org (organization_id, person_id, name, boss_id)This is a typical tree depiction, where boss_id points to a person_id. If boss_id is null, this person owns the company.Now when the user modifies the org table (via a web interface), I do NOT want the results published online to the rest of the world yet. So I create a second table that looks just like the org table:org_published (organization_id, person_id, name, boss_id)The org_published table is what is used to build the webpage seen by the rest of the world. So when the admin user makes modifications to the org chart, the changes are saved in the org table. Then, after they are ready to publish, they click the PUBLISH button.At this point, I need to sync up the org_published table with the org table. What is the best way to do this??? Do I just use brute force SQL, like:(this is pseudo code obviously)qryResults = SELECT * FROM org WHERE organization_id = @orgIDDELETE FROM org_published WHERE organization_id = @orgIDINSERT INTO org_published USING qryResults Or is there a better, cleaner way to handle this? |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-30 : 19:25:35
|
quote:
qryResults = SELECT * FROM org WHERE organization_id = @orgIDDELETE FROM org_published WHERE organization_id = @orgIDINSERT INTO org_published USING qryResults
What DBMS are you using?Jonathan Boott, MCDBA{0} |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-07-30 : 19:25:53
|
lynnbt,Seems OK, but can you simply add another column to the org table to indicate that this data should be published?eg.alter table add Publish bit not null default(0) When inserting new records this defaults to "Not Publish"Add a trigger on the "Update" of this table to set Publish to False..Create a View for Published and one for NotPublishedWhe you are ready to publish run something like..Update NotPublished set Published = 1 where Org_ID = ? (Make sure the "WITH CHECK OPTION" is not set when creating this view, it is the default anyway...)HTHDavidM"SQL-3 is an abomination.." |
|
|
|
lynnbt
Starting Member
9 Posts |
Posted - 2002-07-30 : 19:32:55
|
| Jonathon, I'm using sql server 2k.byrmol, on first pass, that's how we were gonna do it. But it gets more complex than that b/c the hierarchical structure can change. For example, say a piece of the organization undergoes a complete overhaul, so people start reporting to different people, some people get fired, some people get promoted, etc, etc. Well then pieces of the hierarchical structure get changed .... so if Tim report to Joe, and now he reports to Sally, and Joe gets fired -- it can get complex really quick and it becomes more difficult than just using a published flag. Make sense? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-07-30 : 19:35:16
|
| It does make sense if you only want to publish certain parts of an organisations tree.Is that what you want or is it the whole orgainisation or nothing at all?DavidM"SQL-3 is an abomination.." |
|
|
|
lynnbt
Starting Member
9 Posts |
Posted - 2002-07-30 : 19:47:30
|
| Detailed example ....Suppose my org table and org_published table start like this:org_id person_id name boss_id111 1 Joe (null)111 2 Tim 1 111 3 Sally 1111 4 Bob 2111 5 Rick 2111 6 Tony 2111 7 Ashley 3222 8 Jay (null)(more data for other orgs)Then these events happen:- Joe gets fired and Sally takes over as CEO- Ashley gets promoted to take over for Sally - Rick is transferred and now reports to Ashley- Tony is fired- A new person (Anne) is hired and they created a new dept ... Anne reports to the CEOSo now we have:org_id person_id name boss_id111 2 Tim 1 111 3 Sally (null)111 4 Bob 2111 5 Rick 7111 7 Ashley 3111 9 Anne 3222 8 Jay (null)(more data for other orgs)And when we publish, we need to get this data into the org_published table. Hope this helps a little .... you can see how complex it can get. If we do a complete refresh of the published table we are ensured things are the same -- bit this just seems messy to me. I'm open to any ideas! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-07-30 : 20:08:15
|
| lynnbt,So while changes are being made to Org, the Web user sees the Org_Publish table (which doesn't have the changes yet.). Is that a correct assumption?DavidM"SQL-3 is an abomination.." |
|
|
|
lynnbt
Starting Member
9 Posts |
Posted - 2002-07-30 : 20:11:36
|
| You got it ....We have some other approaches we are looking at that are more complex from a developer perspective, but they do not require an additional "published" table. The approach I have described in this thread is pretty straight forward, but just seems ugly (i.e., deleting all relevant data from a table then repopulating it). |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-07-30 : 20:34:57
|
| lynnbt,I am glad we got that straight. It was the most important point...You solution, while intensive in IO and logging is IMHO the best approach. Another approach would be a series of Update/Insert/Delete statments but as you point out that will be complex..HTHDavidM"SQL-3 is an abomination.." |
|
|
|
lynnbt
Starting Member
9 Posts |
Posted - 2002-07-30 : 23:23:52
|
| Thanks for the feedback .... I know this "brute force" solution isn't ideal but it gets the job done and from a developer perspective is very straight forward .... just thought I'd see if anyone else had some unique ideas I was overlooking.Thanks again. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-07-30 : 23:40:13
|
| No worries..I always sacrifice speed for data accuracy...DavidM"SQL-3 is an abomination.." |
|
|
|
|
What is the best way ...