[MiniProject] Part 2: The Refactor

Database Magic, Design Concepts and Code, Programming, Projects Add comments

Aug 142008

As with all good data schemas, there’s usually some refactoring as time goes by. This project isn’t immune to the shifting sands of time or perfection, and so today’s second instalment features a schema change.

Below is a representation of the schema shift. To summarise, separate Actor/Director tables have been merged into one single table, Person, and two join/junction tables have been defined – MovieActor and MovieDirector on the basis that a person may act in and/or direct a film (example, Clint Eastwood).

You will see why this change was made shortly.

Check back for updated T-SQL.

Rob Sanders

IT Professional with over 16 years experience, 13 in commercial software development and 8 years in IT consulting. Check out the "About Rob" page for more information.

Leave a Reply Cancel reply

Featured Articles

A quick and dirty Rules Engine using Windows Workflow (Part 2)
Hi there and happy new year. 2011 promises to be quite an interesting year, and I hope that I can continue to contribute here at Sanders Technology. To kick off the new year, I decided to revisit the Windows Workflow article I started late last year. A little while ago I wrote a post at entitled “A quick and dirty Rules Engine using Windows Workflow (Part 1)” which has evidently been fairly popular. Unfortunately, it seems that I forgot to follow it up with a part 2! Now, welcoming in the new year, I’m putting together the second part. Honestly though, folks, this could easily be a multi part mini project, because the uses of this Windows Workflow Foundation (WF) [...]
A quick and dirty Rules Engine using Windows Workflow (Part 1)
Recently I put my mind towards developing a fairly light rules based utility for applying various logical patterns on top of some linear business data. Honestly, my initial thoughts were “oh no, not another boring rules based implementation” because we (ought to) know how boring that can be – and convoluted – but this time I decided to try something a little different. Through no fault of my own, I’ve actually had very little to do with Windows Workflow Foundation – WF, not WWF which stands for World Wildlife Foundation, and not to be confused with the World Wrestling Federation, just to avoid confusion! Most of the project I’ve worked on previously have used some other workflow product, usually something [...]
The ADO.Net Entity Framework, POCO Objects and You
Introduction Now if you are like me, you’ve probably had some interest in POCO (plain old CLR objects) objects for at least some time. They are an invaluable tool in the distributed systems and service oriented architecture areas, but up until now they’ve been inaccessible for those designs. In a nutshell, both LINQ to SQL and Entity Framework (v1) class entities did not support serialization for the purpose of stateless transport(such as web service communication). This stems from the embedded context tracking attributes, and the design which stipulates a fairly poor experience for those daring enough to detach entities and “pass them around”. Enter the ADO.net Entity Framework v2.. ahem, version 4 which shipped in the early part of this [...]
A Dynamic Data Website with SQL Azure and the Entity Framework
This is part of a series of entries written about Microsoft’s new SQL Azure database service and the Entity Framework v4. Following on from my previous posts (check them out before continuing) – this article assumes you have followed steps outlined in the previous posts to create various models and accounts etc. Part 1 – Working with Entity Framework v4 and SQL Azure Part 2 – Next Steps with SQL Azure Part 3 – Putting it all together – SQL Azure Part 4 – A Dynamic Data Website with SQL Azure and the Entity Framework (this post) Addendum – Getting a SQL Azure Database Continuing along.. Our next step is to create a Dynamic Data website. If you haven’t come [...]
Putting it all together – SQL Azure
Happy Australia Day to you! Following on from my previous posts (check them out before continuing) – this article assumes you have followed steps outlined in the previous posts to create various models and accounts etc. Part 1 – Working with Entity Framework v4 and SQL Azure Part 2 – Next Steps with SQL Azure Part 3 – Putting it all together – SQL Azure (this post) Part 4 – A Dynamic Data Website with SQL Azure and the Entity Framework Addendum – Getting a SQL Azure Database Now we should have a working data model which has been created in your SQL Azure (Cloud) database. Taking lessons learned from Part 2, we’re going to create an Entity Framework (v4) [...]
Working with Entity Framework v4 and SQL Azure
This post is the first in a series of posts on this topic. The following is a summary of the various parts. Part 1 – Working with Entity Framework v4 and SQL Azure (this post) Part 2 – Next Steps with SQL Azure Part 3 – Putting it all together – SQL Azure Part 4 – A Dynamic Data Website with SQL Azure and the Entity Framework Addendum – Getting a SQL Azure Database Introduction Well, it’s been “out there” for a while, and now it is time I gave you a taste of how to combine some of the “Next Generation” data access technologies, today. SQL Azure is Microsoft’s “Database in the Cloud” and represents a complete makeover (replacement) [...]
Functional Transparency: Part 2
This is a little late – but I feel it is worth going into and you can catch up be reading (or re-reading) Part 1 located here. Just in case you are wondering “what on Earth is he babbling on about?” which is a fair and often under asked question, here’s a brief summary to refresh your memory. The objective is to implement additional (background) functionality which is not seen by calling/consuming code – hence, transparent. In fact, it’s even possible to enforce execution of certain functionality no matter how a DataContext is manipulated – neat, huh? So let us look at a simple database table and let us further assume you have generated a LINQ to SQL Model: SQL [...]
Functional Transparency with LINQ to SQL (Part 1)
I’m going to coin the phrase ‘Functional Transparency’ because it fits the definition. I’ve been working with functional transparency in LINQ to SQL for a few weeks now, and it seems to be working well enough to write about. The objective is to implement additional (background) functionality which is not seen by calling/consuming code – hence, transparent. In fact, it’s even possible to enforce execution of certain functionality no matter how a DataContext is manipulated – neat, huh? In other words, we implement additional functionality between the DataContext, its entities and the Database itself. Our objective may be to modify the data returned from a query (or data saved to a database), might be to audit operations or even check [...]
Intelligent Paging using LINQ to SQL and the LinqDataSource control
Undoubtedly, anyone who has evaluated LINQ to SQL has fond it a fairly powerful yet lightweight ORM technology which is less complex than the ADO Entity Framework yet utilizes the strength and power of Language Integrated Queries. One problem with LINQ to SQL is the auto paging feature of the LinqDataSource. Below is a rough GridView which displays three columns, UserName, FirstName and LastName. This is just a rough demo, so we’re looking at paging. If you simply drop a GridView and a LinqDataSource control onto a Web Form and configure the LinqDataSource (using Smart Tags) without specifying a Group By field or Order By field (Figure 1) then you will get fairly optimal database querying (Figure 2) although [...]

« Previous Post Pause Next Post »