September 10, 2014 at 8:37 am
I have two tables, Schools and SchoolHistory. This is basically in a simple design on what is going on currently in a DB of mine.
Let say this is a snapshot of my DB tables on May 30, 2014
--=======================================================
--====================== 1st Table ======================
--=======================================================
--- This table captures the name of the school, how many students, how many courses offered and what year.
--===== If the table already exists, drop it
IF OBJECT_ID('TempDB..#Schools','U') IS NOT NULL
DROP TABLE #Schools
--===== Create the table
CREATE TABLE #Schools
(
[School_ID] [int] IDENTITY(1,1) NOT NULL,
[School_Name] [nvarchar](50) NOT NULL,
[Total_Students] [int] NOT NULL,
[Courses_Offered] [int] NOT NULL,
[YearOf] [int] NOT NULL,
CONSTRAINT [PK_Schools] PRIMARY KEY CLUSTERED
(
[School_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--===== Insert the test data into the table
INSERT INTO #Schools
(School_Name, Total_Students,Courses_Offered, YearOf)
SELECT 'Lincoln','500','20','2013' UNION ALL
SELECT 'Washington','400','15','2013' UNION ALL
SELECT 'Madison','600','30','2013'
--=======================================================
--====================== 2nd Table ======================
--=======================================================
--- This table captures derives a value from the FK_School_ID and has a relationship to the School_ID in the Schools table, captures the name of the sport, how many participants and the revenue it generated.
--- Yes, I do realize I dont have a constraint relationship between FK_School_ID and School_ID for now.
--===== If the table already exists, drop it
IF OBJECT_ID('TempDB..#SchoolHistory','U') IS NOT NULL
DROP TABLE #SchoolHistory
--===== Create the table
CREATE TABLE #SchoolHistory
(
[School_History_ID] [int] IDENTITY(1,1) NOT NULL,
[FK_School_ID] [int] NOT NULL,
[Sport] [nvarchar](50) NOT NULL,
[NumOfParticipants] [int] NOT NULL,
[Revenue] [money] NOT NULL,
CONSTRAINT [PK_SchoolHistory] PRIMARY KEY CLUSTERED
(
[School_History_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--===== Insert the test data into the table
INSERT INTO #SchoolHistory
(FK_School_ID, Sport,NumOfParticipants, Revenue)
SELECT '1','Basketball','10','10000.00' UNION ALL
SELECT '1','Football','50','50000.00' UNION ALL
SELECT '3','Volleyball','15','5000.00' UNION ALL
SELECT '3','Hockey','30','9000.00' UNION ALL
SELECT '2','Wrestling','20','11000.00' UNION ALL
SELECT '2','Baseball','25','15000.00'
If I build a SQL query to find out at that point of time of May 30, 2014 the relationship between the two tables it would look like this:
SELECT #SchoolHistory.School_History_ID,#SchoolHistory.FK_School_ID,#SchoolHistory.Sport,#SchoolHistory.NumOfParticipants,#SchoolHistory.Revenue,#Schools.School_Name,#Schools.Total_Students,#Schools.Courses_Offered,#Schools.YearOf
FROM #Schools
LEFT OUTER JOIN
#SchoolHistory ON #Schools.School_ID = #SchoolHistory.FK_School_ID
order by School_History_ID
--- Output should look like this:
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '2' as School_History_ID,'1' as FK_School_ID,'Football' as Sport,'50' as NumOfParticipants,'50000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '3' as School_History_ID,'3' as FK_School_ID,'Volleyball' as Sport,'15' as NumOfParticipants,'5000.00' as Revenue,'Madison' as School_Name,'600' as Total_Students,'30' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '4' as School_History_ID,'3' as FK_School_ID,'Hockey' as Sport,'30' as NumOfParticipants,'9000.00' as Revenue,'Madison' as School_Name,'600' as Total_Students,'30' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '5' as School_History_ID,'2' as FK_School_ID,'Wrestling' as Sport,'20' as NumOfParticipants,'11000.00' as Revenue,'Washington' as School_Name,'400' as Total_Students,'15' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '6' as School_History_ID,'2' as FK_School_ID,'Baseball' as Sport,'25' as NumOfParticipants,'15000.00' as Revenue,'Washington' as School_Name,'400' as Total_Students,'15' as Courses_Offered,'2013' as YearOf
Here is my dilemma, I changed a few records in my Schools table. Lets say its for School_ID 1.
Originally as of May 30, 2014, it looked like this:
SELECT '1' as School_ID,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf
As of June 30,2014, it looked like this:
SELECT '1' as School_ID,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf
Now if I rerun my SQL query that has a relationship between the two tables it would look like this for this particular record.
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '2' as School_History_ID,'1' as FK_School_ID,'Football' as Sport,'50' as NumOfParticipants,'50000.00' as Revenue,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf
As you can see, when I update the Schools table, I lost my history of the main information prior to June.
I know this was a bad table design. At the time of development, I thought the Schools table would stay static and wouldn't change. My assumption proved incorrect and now I have the task in trying to capture the history of the Schools table changes and make sure it stays consistent in my SchoolHistory table.
Ideally, this is what my History table records would kinda look like:
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '2' as School_History_ID,'1' as FK_School_ID,'Football' as Sport,'50' as NumOfParticipants,'50000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '3' as School_History_ID,'3' as FK_School_ID,'Volleyball' as Sport,'15' as NumOfParticipants,'5000.00' as Revenue,'Madison' as School_Name,'600' as Total_Students,'30' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '4' as School_History_ID,'3' as FK_School_ID,'Hockey' as Sport,'30' as NumOfParticipants,'9000.00' as Revenue,'Madison' as School_Name,'600' as Total_Students,'30' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '5' as School_History_ID,'2' as FK_School_ID,'Wrestling' as Sport,'20' as NumOfParticipants,'11000.00' as Revenue,'Washington' as School_Name,'400' as Total_Students,'15' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '6' as School_History_ID,'2' as FK_School_ID,'Baseball' as Sport,'25' as NumOfParticipants,'15000.00' as Revenue,'Washington' as School_Name,'400' as Total_Students,'15' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '7' as School_History_ID,'1' as FK_School_ID,'Track' as Sport,'60' as NumOfParticipants,'8000.00' as Revenue,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '8' as School_History_ID,'1' as FK_School_ID,'Soccer' as Sport,'25' as NumOfParticipants,'7000.00' as Revenue,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf
Hope this gives you an ideas what it should have looked like if I added a couple of more records.
As you can see my goal is to keep the past history information and changes in the Schools table will reflect the same information when records are added in the SchoolHistory table.
Any thoughts on how I can fix this? I hope I was clear in my explanation. If not feel free to ask additional questions. I am assuming I will have to go back to a point in time and restore the database and copy information to my current database.
Thanks. I'd appreciate any help.
September 10, 2014 at 9:04 am
I'm not sure I completely understand. You're giving data, but you haven't explained what in the data you'd like changed, so I'm not sure I'm completely understanding. It's good to see the data, but without explanations of what the relationships are, or how you use this, it's hard to know.
For example, your expected data seems to show different sports than in the first selects, with the new counts.
My first instinct is to say this needs to be like a Type II (? I think) table. You really need some dates in the school and history tables that mark the valid to/from dates when things apply. If you had some dates of when items were entered in School and then valid dates for SchoolHistory, you could match on the PK and also the dates to determine which counts are to be used.
September 10, 2014 at 10:22 am
Steve Jones - SSC Editor (9/10/2014)
I'm not sure I completely understand. You're giving data, but you haven't explained what in the data you'd like changed, so I'm not sure I'm completely understanding. It's good to see the data, but without explanations of what the relationships are, or how you use this, it's hard to know.For example, your expected data seems to show different sports than in the first selects, with the new counts.
I apologize if I wasn't clear. Basically my relationship between the two tables derives the FK_School_ID (SchoolHistory table) and the School_ID (Schools table)
So what I have given prior, I will use a particular record. Lets look at the SchoolHistory table with the School_History_ID of 1
This table uses
School_History_ID as a primary key incremental counter.
FK_School_ID is a foreign key which has a relationship to the School_ID in the Schools table.
Sport, NumofParticpants, Revenue are USER inputs.
So how I would populate this table to indicate which school is this record associated with (FK_School_ID) and log the type of sport, NumOfParticpants and the revenue.
First entry (School_History_ID 1):
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue
In my first entry in my Schools table it would look like this (School_ID 1):
SELECT 'Lincoln','500','20','2013'
If I was to create a query to find out the relationship between the two tables, it would look like this as of May 30, 2014
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf
Lets say some time in mid June 2014, the School ID 1 in my Schools table changed from:
SELECT 'Lincoln','500','20','2013'
to
SELECT 'Lincoln','495','19','2013'
If I reran the same query that shows the relationship between the two tables on June 30,2014, I lost what I originally ran
May 30, 2014
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf
June 30, 2014
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf
As you can see the Total_Students and Courses_Offered changed.
I lost what was originally reported back on May 30, 2014.
Also, when I showed the following data this for demo purpose, I wanted to give you a general idea what I would like it to show with the history of May 30 info intact and if I added an additional 2 records showing the update info from my School_ID 1 (Schools table).
SELECT '1' as School_History_ID,'1' as FK_School_ID,'Basketball' as Sport,'10' as NumOfParticipants,'10000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '2' as School_History_ID,'1' as FK_School_ID,'Football' as Sport,'50' as NumOfParticipants,'50000.00' as Revenue,'Lincoln' as School_Name,'500' as Total_Students,'20' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '3' as School_History_ID,'3' as FK_School_ID,'Volleyball' as Sport,'15' as NumOfParticipants,'5000.00' as Revenue,'Madison' as School_Name,'600' as Total_Students,'30' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '4' as School_History_ID,'3' as FK_School_ID,'Hockey' as Sport,'30' as NumOfParticipants,'9000.00' as Revenue,'Madison' as School_Name,'600' as Total_Students,'30' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '5' as School_History_ID,'2' as FK_School_ID,'Wrestling' as Sport,'20' as NumOfParticipants,'11000.00' as Revenue,'Washington' as School_Name,'400' as Total_Students,'15' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '6' as School_History_ID,'2' as FK_School_ID,'Baseball' as Sport,'25' as NumOfParticipants,'15000.00' as Revenue,'Washington' as School_Name,'400' as Total_Students,'15' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '7' as School_History_ID,'1' as FK_School_ID,'Track' as Sport,'60' as NumOfParticipants,'8000.00' as Revenue,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf UNION ALL
SELECT '8' as School_History_ID,'1' as FK_School_ID,'Soccer' as Sport,'25' as NumOfParticipants,'7000.00' as Revenue,'Lincoln' as School_Name,'495' as Total_Students,'19' as Courses_Offered,'2013' as YearOf
Steve Jones - SSC Editor (9/10/2014)
My first instinct is to say this needs to be like a Type II (? I think) table. You really need some dates in the school and history tables that mark the valid to/from dates when things apply. If you had some dates of when items were entered in School and then valid dates for SchoolHistory, you could match on the PK and also the dates to determine which counts are to be used.
So what is a Type II and can you give me a visual example of using dates per your suggestion? I am a newbie at this so forgive me on my bad design. I am just trying to fix a situation and try to move forward.
Thanks again!
September 10, 2014 at 10:28 am
The biggest issue you have is that you have a base table and audit table but there are no columns to record the time something happened. This means that your audit table is basically just a list of things that happened. You can order it by the audit table primary key but you have no idea when these changes happened. If you want to know when something happened you MUST record the time it happened at the time it happens.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2014 at 10:54 am
Sorry, Type II Slowly Changing Dimension - http://www.1keydata.com/datawarehousing/slowly-changing-dimensions-type-2.html
It's kind of what you have, but not quite.
As Sean mentioned, you need to record time. Without time, it's not clear what happened when or where. As a further question, if the courses changed, and you're trying to track history, then I'm not sure you should be joining to Schools to get this information. If it's important that you know the sport that is offered and what the number was, store that in the table. Going back to the parent for this is a mistake, and it means you're trying to have the data mean different things for different rows.
Thanks for the info, but a couple things when you post. If you want to show results, just show them like this:
School_History_ID FK_School_ID Sport NumOfParticipants Revenue School_Name Total_Students Courses_Offered YearOf
----------------- ------------ ---------- ----------------- -------- ----------- -------------- --------------- ------
1 1 Basketball 10 10000.00 Lincoln 500 20 2013
2 1 Football 50 50000.00 Lincoln 500 20 2013
3 3 Volleyball 15 5000.00 Madison 600 30 2013
4 3 Hockey 30 9000.00 Madison 600 30 2013
5 2 Wrestling 20 11000.00 Washington 400 15 2013
6 2 Baseball 25 15000.00 Washington 400 15 2013
7 1 Track 60 8000.00 Lincoln 495 19 2013
8 1 Soccer 25 7000.00 Lincoln 495
No need to build SELECTs for this. It's harder to read when things scroll, and I can't always get things into SSMS (though I do appreciate the selects). Also, explain what's different clearly. We don't know your data, so it's hard for us to be sure we see the same relationship you see. If there's a problem, tell me that for Lincoln, for Track you want to see 19 as the courses offered, but 20 for Basketball because those were the values when the data was entered.
If you're creating queries to show relationships, it's helpful to show those queries. You have good descriptions, but it's possible you've broken something, and we can look at your code and better understand what's happening on your end. The DDL is great, and thanks for that.
September 10, 2014 at 12:03 pm
Sean Lange (9/10/2014)
The biggest issue you have is that you have a base table and audit table but there are no columns to record the time something happened. This means that your audit table is basically just a list of things that happened. You can order it by the audit table primary key but you have no idea when these changes happened. If you want to know when something happened you MUST record the time it happened at the time it happens.
Could you provide an example how that would be laid out in a base table and audit table format? So I can conceptually see this in action?
Thanks
September 10, 2014 at 12:08 pm
Steve Jones - SSC Editor (9/10/2014)
Sorry, Type II Slowly Changing Dimension - http://www.1keydata.com/datawarehousing/slowly-changing-dimensions-type-2.htmlIt's kind of what you have, but not quite.
As Sean mentioned, you need to record time. Without time, it's not clear what happened when or where. As a further question, if the courses changed, and you're trying to track history, then I'm not sure you should be joining to Schools to get this information. If it's important that you know the sport that is offered and what the number was, store that in the table. Going back to the parent for this is a mistake, and it means you're trying to have the data mean different things for different rows.
Thanks for the info, but a couple things when you post. If you want to show results, just show them like this:
School_History_ID FK_School_ID Sport NumOfParticipants Revenue School_Name Total_Students Courses_Offered YearOf
----------------- ------------ ---------- ----------------- -------- ----------- -------------- --------------- ------
1 1 Basketball 10 10000.00 Lincoln 500 20 2013
2 1 Football 50 50000.00 Lincoln 500 20 2013
3 3 Volleyball 15 5000.00 Madison 600 30 2013
4 3 Hockey 30 9000.00 Madison 600 30 2013
5 2 Wrestling 20 11000.00 Washington 400 15 2013
6 2 Baseball 25 15000.00 Washington 400 15 2013
7 1 Track 60 8000.00 Lincoln 495 19 2013
8 1 Soccer 25 7000.00 Lincoln 495
No need to build SELECTs for this. It's harder to read when things scroll, and I can't always get things into SSMS (though I do appreciate the selects). Also, explain what's different clearly. We don't know your data, so it's hard for us to be sure we see the same relationship you see.
If you're creating queries to show relationships, it's helpful to show those queries. You have good descriptions, but it's possible you've broken something, and we can look at your code and better understand what's happening on your end. The DDL is great, and thanks for that.
Sorry for not providing queries on how I derived the results. I did show one on my OP how I would derive my results. I provided a query and SELECTS to give an option to see the results.
I provided the SELECTs because I had no idea to how to show the results in a column format, just like you provided in your reply example without columns and row data being skewed?
How did you do that format where you get the column headings and rows to display all aligned. Sigh...Still so much to learn on how to visually provide examples for you all. 😀
Hopefully I can find examples like what Sean stated so I can fix this problems.
September 10, 2014 at 1:21 pm
cbernardes (9/10/2014)
Sean Lange (9/10/2014)
The biggest issue you have is that you have a base table and audit table but there are no columns to record the time something happened. This means that your audit table is basically just a list of things that happened. You can order it by the audit table primary key but you have no idea when these changes happened. If you want to know when something happened you MUST record the time it happened at the time it happens.Could you provide an example how that would be laid out in a base table and audit table format? So I can conceptually see this in action?
Thanks
Oye. In looking closer at your tables the names don't at all seem to match the content. You have a table name SchoolHistory which seems to be a denormalized table of sports!?!?!?! This data should all be normalized so it is more manageable.
Start with your base table Schools. It should NOT have TotalStudent, CoursesOffered or Year. Those are not properties of the school. They are properties of a SchoolYear. Just for a school as you have shown I would break this apart into at least 4 tables (School, SchoolYear, Courses, CoursesOffered).
Then for SportsHistory I would break this into a few tables also. I would have Sports, SportsParticipants, SportsRevenue.
To be honest it is really unclear what you are trying to model here. It seems that you need to spend some time researching and understanding normalization. You have duplicated data points and all sorts of denormalized design elements throughout your table structures.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2014 at 1:47 pm
Sean Lange (9/10/2014)
cbernardes (9/10/2014)
Sean Lange (9/10/2014)
The biggest issue you have is that you have a base table and audit table but there are no columns to record the time something happened. This means that your audit table is basically just a list of things that happened. You can order it by the audit table primary key but you have no idea when these changes happened. If you want to know when something happened you MUST record the time it happened at the time it happens.Could you provide an example how that would be laid out in a base table and audit table format? So I can conceptually see this in action?
Thanks
Oye. In looking closer at your tables the names don't at all seem to match the content. You have a table name SchoolHistory which seems to be a denormalized table of sports!?!?!?! This data should all be normalized so it is more manageable.
Start with your base table Schools. It should NOT have TotalStudent, CoursesOffered or Year. Those are not properties of the school. They are properties of a SchoolYear. Just for a school as you have shown I would break this apart into at least 4 tables (School, SchoolYear, Courses, CoursesOffered).
Then for SportsHistory I would break this into a few tables also. I would have Sports, SportsParticipants, SportsRevenue.
To be honest it is really unclear what you are trying to model here. It seems that you need to spend some time researching and understanding normalization. You have duplicated data points and all sorts of denormalized design elements throughout your table structures.
Hi Sean,
I know my example wasnt the greatest at all to post as you saw there were normalization issues. Basically, the idea I was trying to convey was that I had a master table (Schools) and so called a history of transactions (SchoolsHistory). I was creating examples of dummy column data for those tables. I apologize for the confusion.
In my scenario, I was running into an issue everytime a new record was created in the SchoolsHistory table, the relationship ship from the FK_School_ID to the School_ID in the Schools table that it was only going to present what was currently in place. So if any updates occurred in that Schools table, all records related to it in the SchoolHistory table would reflect those current values. I just wanted to make sure I somehow had a way to keep track of historical data, so that when someone queries a record from the SchoolHistory, they know what values were in place from the Schools Table from a particular point in time.
A possible solution I think if I understand your last reply and some further reading on google is that I could have another table called Schools_History table which basically has the same columns with datefields and a Foreign key that it would associate from the Schools table. A trigger would create a new record in the Schools_History table whenever something is updated in the Schools Table.
Then in my SchoolsHistory table, If I have a timestamp for when it was created, I could create a relationship to either the Schools or Schools_History table based on some datefield criteria and Foreign Key of the FK_School_ID
I hope this made sense.
September 10, 2014 at 2:01 pm
Ahh gotcha. OK, back on track. Let's use your Schools table as an example. My former comments about normalization would apply if this is your real table but we shall continue.
You just need to capture all the data in the main table AND a date so you know what the table looked at on a given date.
IF OBJECT_ID('TempDB..#Schools','U') IS NOT NULL
DROP TABLE #Schools
--===== Create the table
CREATE TABLE #Schools
(
[School_ID] [int] IDENTITY(1,1) NOT NULL,
[School_Name] [nvarchar](50) NOT NULL,
[Total_Students] [int] NOT NULL,
[Courses_Offered] [int] NOT NULL,
[YearOf] [int] NOT NULL,
CONSTRAINT [PK_Schools] PRIMARY KEY CLUSTERED
(
[School_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
create table #SchoolsHistory
(
SchoolHistoryID int identity not null,
[School_ID] [int] NOT NULL,
[School_Name] [nvarchar](50) NOT NULL,
[Total_Students] [int] NOT NULL,
[Courses_Offered] [int] NOT NULL,
[YearOf] [int] NOT NULL,
DateModified datetime not null default getdate()
)
Then you could have a trigger on Schools to capture any updates to Schools. Does that make more sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2014 at 2:24 pm
Sean Lange (9/10/2014)
Ahh gotcha. OK, back on track. Let's use your Schools table as an example. My former comments about normalization would apply if this is your real table but we shall continue.You just need to capture all the data in the main table AND a date so you know what the table looked at on a given date.
IF OBJECT_ID('TempDB..#Schools','U') IS NOT NULL
DROP TABLE #Schools
--===== Create the table
CREATE TABLE #Schools
(
[School_ID] [int] IDENTITY(1,1) NOT NULL,
[School_Name] [nvarchar](50) NOT NULL,
[Total_Students] [int] NOT NULL,
[Courses_Offered] [int] NOT NULL,
[YearOf] [int] NOT NULL,
CONSTRAINT [PK_Schools] PRIMARY KEY CLUSTERED
(
[School_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
create table #SchoolsHistory
(
SchoolHistoryID int identity not null,
[School_ID] [int] NOT NULL,
[School_Name] [nvarchar](50) NOT NULL,
[Total_Students] [int] NOT NULL,
[Courses_Offered] [int] NOT NULL,
[YearOf] [int] NOT NULL,
DateModified datetime not null default getdate()
)
Then you could have a trigger on Schools to capture any updates to Schools. Does that make more sense?
Thanks, it is making more sense. Now I think I know what I will have to do now. I appreciate you walking this through with my bad example.
September 10, 2014 at 2:40 pm
cbernardes (9/10/2014)
Sean Lange (9/10/2014)
Ahh gotcha. OK, back on track. Let's use your Schools table as an example. My former comments about normalization would apply if this is your real table but we shall continue.You just need to capture all the data in the main table AND a date so you know what the table looked at on a given date.
IF OBJECT_ID('TempDB..#Schools','U') IS NOT NULL
DROP TABLE #Schools
--===== Create the table
CREATE TABLE #Schools
(
[School_ID] [int] IDENTITY(1,1) NOT NULL,
[School_Name] [nvarchar](50) NOT NULL,
[Total_Students] [int] NOT NULL,
[Courses_Offered] [int] NOT NULL,
[YearOf] [int] NOT NULL,
CONSTRAINT [PK_Schools] PRIMARY KEY CLUSTERED
(
[School_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
create table #SchoolsHistory
(
SchoolHistoryID int identity not null,
[School_ID] [int] NOT NULL,
[School_Name] [nvarchar](50) NOT NULL,
[Total_Students] [int] NOT NULL,
[Courses_Offered] [int] NOT NULL,
[YearOf] [int] NOT NULL,
DateModified datetime not null default getdate()
)
Then you could have a trigger on Schools to capture any updates to Schools. Does that make more sense?
Thanks, it is making more sense. Now I think I know what I will have to do now. I appreciate you walking this through with my bad example.
Great news. Glad it is making sense now. Sorry I got so mixed up in the example that I lost sight of the question. If you do the trigger make certain you do NOT use variable in your trigger. That is a clear sign that the trigger is not going to handle multiple row operations. Read up on triggers and use the inserted and deleted virtual tables available in your trigger. Feel free to post back to this question if you find yourself getting stuck. Myself or many other countless people will be happy to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2014 at 7:04 pm
I don't love the way you have schools and the year in there, but if you're not up for a big refactoring of the tables, then Sean's suggestion makes sense.
Please take care in writing triggers and test them with 0, 1, and 2 (or more) row updates to be sure it's acting as you expect.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply