Question on Creating SQL Server 2K Relationship

[Guest]

I have a rather large table with several dozen fields, two of which are "location" and "goal."   Most of the goals are already populated.

We now realize we need to create another table, a master "goals table" that contains just the "location" and the "goal."  I want to set this up, so that when you update a goal in the master table, it automatically updates the goal in the big table.

I know how to create a relationship between the two tables, on "location" and "goal."  But the big table has already been created, and has the goals already populated.

My question is, when I create the new, 2-column master goals table, and the relationship, will it INITIALLY, and automatically do an UPDATE on the goals in the big table?  Or does this have to be done initially, when both tables are first created?

I need for the goals in the big table to be updated as soon as I create the master goals table, and then continue to change in the future, whenever the goals are changed in the master goals table.

Will simply creating a relationship do this?  Or do I have to do something else, (like run an update query) to "force" the goals in the big table to automatically UPDATE, and then stay updated, each time a goal in the master goals table is changed?  My SQL Server book doesn't tell you this.  It only explains how it works when you FIRST create the tables, and BEFORE you populate the goals in either table.

I am getting ready to experiment with this on a backup copy of the table.  But was hoping to find out, before moving forward.  Thanks for any help/advice.

Gary

[Dan_Bracuk]

I'm not sure why you would be creating one table instead of two.  Separate tables for goals and locations might make more sense.  But, it's your application.

I suggest that you do the following in the order specified.

1.  Create your orders_goals table.  Make sure there is a third column to act as the primary key.  Add a single row which will act as your default.

2.  Add a column to your big table.  Give it a foreign key reference to your new table.  Give it a default value.

3.  Populate your new table.

4.  Rewrite any queries that populate or select these fields from your big table.

5.  Update the new column in your big table.

6.  Drop the existing columns in your big table.

[Guest]

Dan, thanks for the prompt answer.  I think I should have simplified my question, and explained it a little better.  I'm just looking for the answer to a few simple questions, which is whether or not, WHAT I WANT TO DO, can be done?  If so, I'll do the work to make it happen (learning as I go, thanks for your suggestions).  Let me try one more time on the question, with a brief description of problem first:

1.  My dept has been using 1 big table that stores monthly performance metrics, along with monthly goals, for every location.  The staff has been going into the big table, and updating monthly goals manually.

2.  Someone on my team, already created a master goals table, that contains the "metric", each of which has a goal.  This goals table stores the metric (unique primary key), along with a monthly goal (1 column for the month), the store location ID number, and of course, the goal.

By the way, we also have a 3rd table, called the master "metric" table, which stores all the metrics being measured, including a metric code (unique primary key) and official metric name.  The BIG table has both the "metric", the "metric code", the location, the month, AND the goal, (among other columns).

Lastly, the BIG table has one primary key, called ID, which is just a unique identifier number, in the first column.

MY QUESTION IS THIS:  Just given the above, without creating or modifying the existing tables, IS IT POSSIBLE,

USING NOTHING BUT RELATIONSHIPS, TO AUTOMATICALLY UPDATE THE GOALS IN THE BIG GOALS TABLE, BY UPDATING THE GOALS IN THE MASTER GOALS TABLE?

I just need to know if this CAN BE DONE?  The relationship between the GOALS table, and the BIG table, would be based on more than one item.  I've never done a relationship on more than one item.  I'M ASSUMING THIS CAN BE DONE, CORRECT?  (This is QUESTION 2).   If so, it would be based on "metric", "location", "month", and "goal."  This would ensure the correct row (location, metric, month, and goal) in the BIG table is updated.

Assuming this is possible, my first step would be to go into the GOALS table, and create the relationship to the big table.  But it needs to be based on 4 items, as mentioned:  metric code, location, month, and goal.

I noticed in SQL Server that, when you first create a relationship, it asks if you want to "initially" update the records in the foreign table.  The answer is YES.  We want the goals in the BIG table to be updated immediately, after creation of the relationship.  Then, have all future goal updates, done automatically whenever someone changes a goal in the goals table.

IS THIS POSSIBLE?  If so, then I just need a few tips on how to ensure the relationship is created initially, so this process works.  Thanks very much for your help.

Gary

[Guest]

Dan, I figured it out, thanks for the advice.  I'm pretty good with SQL, just haven't had much need to work with relationships until now.  I was using my SQL Server 2000 training book, and found out that you can update the values in one table (my case, the goals in the big master table, the foreign table), by updating the goals in the smaller goals table (the primary key table).......just by creating relationships, and enabling cascading UPDATES.  The reason it wasn't working, is that the goals in the master (foreign) table were NULL.  Updating values in the primary key table, won't update the related valued in the foreign table, IF THEY ARE NULL.

So when I ran a one-time UPDATE query, to put the goals from the goals table into the master table, all the updates worked AFTER that.  Not sure why it wouldn't update the NULL values, but no big deal after the one-time UPDATE query.

But I do have one last question, please. The relationship is based on more than one value, it's based on 1. Location, 2. Month, and 3. Metric (item being measured).  For each record in both the goals and master table, there are 3 goals per month, a month, quarter and annual goal.

Is it best to create ONE relationship that includes all 3 goals (the month, quarter and annual)?  Or create 3 relationships, one for each goal?  Since all 3 need to be "in sync", it would seem doing it with ONE relationship would be the most efficient.

So the question is:  If you have more than one related value between the primary key table, and the foreign table (in my case 3 goal values per record), is it best to have one relationship that includes all 3 values?  Or have 3 relationships, one for each related value (one for the month goal, one for quarter, one for annual)?  Just want to make sure there aren't any advantages or disadvantes I might not be considering.  Thank you again,

Gary