Lots of bits of paper bound together with words in it. This is fine for humans, but if you wanted to describe a book in such a way that a computer could find information about it for you you would need to determine what the unique attributes of the book are. Before hitting the Return key, we probably want to look a little closer at what we are storing here. Looking at these attributes you may notice that some will be shared by more than one book.
For instance, an author may have written multiple books. A publisher, unless the book is self-published, is likely to have published many books. This gives us the opportunity to save a lot of storage space, and to improve our database design. After all we are using a Relational Database system, so lets create some relationships. What about Author? Really an author has at a minimum a name. They are likely to have a first name and a last name, and perhaps a middle initial. For our purposes though, lets keep it simple with just a first and last name. If you are looking for a John Grisham novel, you would probably search for Grisham, and not John as that would match way too many authors not related to the John you are after.
So lets first create our author table:. There is a lot more there than just a first and last name. What's going on? Taking it one step at a time:. This is one of the more important thing you need to understand about relationships. Picking the right field to relate. When we finally create the book table we need to be able to identify which author we are talking about.
So we need a common field, one that exists in both tables. With just first name and last name, we are going to have problems. For instance both Merrilee Moss and Tara Moss write books. Both Peter Temple and Peter Corris write books. So neither first name nor last name are unique. This is where id comes in. It can uniquely identify an author record.
This is the other requirement of a relation field. It needs to be indexed, and it needs to be a unique index. KEY is the keyword in SQL that identifies that this is an index, and the name of the field or fields, we can have more than one that forms the index is in brackets.
Fangirl Musings’s 'classified-as-dnf' books on Goodreads (63 books)
Not only are they unique, but as the name implies there can only be one per table. Some storage engines make special arrangements for primary keys to improve the speed at which data is retrieved. For instance, the InnoDB engine stores the primary key along with the full row of data for a record. It doesn't create a separate index. This means that primary key lookups are very fast as there is no need to go looking for the data associated with the key.
Since the most common lookup of an author is going to be by last name, this index makes a lot of sense. This ensures we can't have an empty last name, which wouldn't make a lot of sense.
Note how similar these two tables are. Both have a name we can search on, both have a TEXT field that allows us to store wait for it This should be enough to start with. We can refine our design later, but for now lets create the book table, using the relationships we've now defined with the above tables. Now we have a book table that uses integer values that match the id fields in the related tables. In this example we've used the table name of the related table as the field name of the relation. It doesn't really matter, provided you are consistent. Just on consistency I've also made all the table names singular.
Note that we have an id field in our book table as well. This will be useful later. The format of the fields that link tables should have the same format as the field they link to.
In this case I've made this the number 0, but since this cannot be a valid id value, this is really a catch-all. It might have been better creating 'UNKNOWN' entries in the author, publisher and format tables and using the id number of those records as the default. Even better would be not to supply a default as this would force there to be a value that should match a valid record in the parent tables. Time to see what the database structure looks like. We have a few options here.
This flag means that each new record that doesn't explicitly set the id field will have it set to the next sequential number greater than the highest used number in that field. Time to add some data. First of all the reference tables. These are the author, publisher and format that are referenced from the book table. In just one query we've added four authors.
Of the Muses
A few quick ones to get us rolling, note I'm not filling in the notes field yet, we'll deal with that in a later episode. Notice in the case of the format table I've used an alternate syntax, it does the same thing, but is often easier to understand than the VALUES syntax. We have the basic table information here, so now lets put in a book or two, we'll use the SET syntax to make it a little easier to follow.
Before we can start though, we need to find out the ids that were generated when we added the above data, so we need a quick query. Otherwise we would need to use a comma separated list of fields we wanted to see. Now we have everything we need to create some books. Now we need to structure that so the database can store it:.
You'll be starting to see a pattern developing here. It's a short read, but nevertheless chock-full of great tips and techniques for creating Web sites that dazzle the eyes. If you haven't read this one yet, do yourself a favor and order it today. A few weeks ago, it occurred to me that Dan's sample site would also make a great example for demonstrating Web standards design in SharePoint. Since it only contains two pages, deployment should be a breeze.
Furthermore, the design work was done by actual "Web Designers" i. Dan and his co-author, Ethan Marcotte who -- and don't take this the wrong way -- are probably completely oblivious to SharePoint. In truth, I'd really like to see the site running in SharePoint Server , but I fully recognize that many of you out there may still be running the older version.
I've talked before about how I like to use "scenarios" and "interim milestones" when tackling projects. Whether you prefer to call these "user stories" a. The point is, the key to successfully delivering technology solutions -- at least in my experience -- is "biting off" a reasonable chunk that can be delivered within a short period of time. With that in mind, the following scenario was identified for the first milestone - or, "M1" as I like to call it:. I subsequently created a new Visual Studio solution and added my "standard" list of project items e.
For "Sprint 1", the Development team which is comprised entirely of one person -- me committed to the following product backlog items:. Here are the corresponding work items that I added to the TFS project note that these are in addition to the "default" work items listed in my previous post :. The "create prototype" task was a really easy one to complete in this particular case since Dan and Ethan did all the work already , but an important one nonetheless. When developing a Web site that will run in SharePoint, I almost always try to have a static HTML prototype that I can refer to and use for quickly mocking up new features.
The next task was to create the master page for the Tugboat site. I started with the minimal master page provided on MSDN and then applied some of the changes described in part 1 of this series, including inserting some additional ContentPlaceHolder controls just in case I ever want to use the Tugboat master page in place of application. Note that I added the master page in the Tugboat. Layouts feature which is very similar to the out-of-the-box PublishingLayouts feature. Then I added a Themes folder to the feature along with "Theme1" containing the CSS files and corresponding images from the Tugboat sample site.
At this point, the Tugboat. Layouts feature looked like this:. Note that I only included the images in the Theme1 folder that are referenced in the corresponding CSS files. There are a number of other images in the Tugboat sample site, but there are better places to put these, depending on their usage. More on that later. Also note that one of my goals with the SharePoint solution was to leave the CSS developed by Dan as pristine as possible meaning I tried to keep them as-is -- despite my desire, for example, to merge them or provide better filtering of IE-specific rules.
Westward Ho! Musing on Mathematics and Mechanics
When I originally created the Tugboat master page, it was obvious which sections of the page would be rendered by the master page and which sections would be rendered by the actual page content. Specifically, the masthead a. However, it wasn't immediately obvious how the actual page content would be specified.
My first thought was to simply use the PageContent field for the main content i. Drink Menu and Press. However, there was one fundamental problem with that. Any guesses? Here is a "wireframe" I put together to help visualize the various fields and Web Parts on the Tugboat home page:. When creating the page layout, I could have just specified two Web Part zones and the PageContent field since that is all that is required in this particular case.
- Just the Bible On... Christmas.
- Family history; croquet; book-binding; Baconiana & Elizabeth Poston.
- dead caulfields;
- Creation and the Persistence of Evil?
- Au nom de la Loi : roman (Littérature Française) (French Edition).
- Candida Moss | The Musings of Thomas Verenna;
In fact, I've been involved in some SharePoint projects where this sort of "minimal" approach has been used. However, I'm not a fan of it, and here's why Suppose that Tugboat decides to change the home page of the site in order to add something new below the page content; or perhaps after Sprint 1 we decide to continue rendering the "Weekly Specials" using the PageContent field, but render the "News" section from a SharePoint list via a Web Part.
While we could certainly change the page layout in the future -- or add a new page layout to replace the existing one -- my preference is to provide a small number of "robust" page layouts even to the point where a two-column page layout like the one illustrated above actually "collapses" into a single column, in the case where one of the columns is empty. Well, actually, in the end I'll only be using the Top Right Zone.