Todd Kleinhans (b/t) is host for T-SQL Tuesday this month and wants us to talk about how we use databases in our day to day life.
You know, it’s funny, a lot of these T-SQL Tuesday posts are difficult for me. Which is probably one of the reasons I try so hard to do one each month. This one is no exception. I don’t use databases at home all that often. I mean we have an Excel spreadsheet with a list of movies we have. But not only do we not keep it up but I think I’d be crucified for calling a spreadsheet a database. I use databases quite a bit for blogging but I feel like that was excluded by the statement outside of work and learning. That really leads only one other choice. I could argue that it’s also covered by both outside of work and learning but I’m going with it anyway.
I write crossword puzzles
I quite enjoy writing these things. I’ve written them for fun, for quizzes at the end of sessions, and some for pay. I think they are a great way to reinforce what you know about SQL Server. That said, they do take some work. To build the actual crosswords I use an online tool (https://crosswordlabs.com/) but before I get to that point I need to come up with the words and the clues. Depending on the complexity of the crossword I’ll range from 15-30 clues in the crossword. By preference, I come up with at least 5-10 more and then get a random selection. In order to store all of this, I needed a database.
To start with here are some of the requirements:
- Store words & clues.
- A word could have more than one clue.
- I want to be able to do puzzles by category.
- A word/clue pair can be in multiple categories.
- A word/clue pair can be used multiple times.
- I want to know if a word/clue pair has been used and if so how long ago.
- I want to add new word/clue/category combinations easily.
- I want to be able to pull random word/clue lists by category and usage.
I should probably warn you that my skill at database design is at best mediocre. Regardless this is what I came up with:
In order to make sure the words are unique there is a unique key on the Word column. Same with Categories.Category. There is the possibility that a clue could hit more than one word but I decided that in that case, I’d just have two entries. Not exactly normalized but it’s not going to happen very often so I decided not to worry about it. Regardless I didn’t make the column unique. The Use column isn’t unique because it’s just a description. I’m not overly worried about overlap.
To make adding data easier I have a stored procedure to add a Word. It checks to see if the word exists and if doesn’t add it, then regardless it passes back a WordId. Next is a sp to add Clues. This SP takes a word/clue pair as parameters. It uses AddWord to check if the word exists and get back the WordId then checks if the clue exists, adds it if needed, and regardless returns the ClueId. And of course, the AddCategory sp uses the AddClue sp to do the same thing for Categories.
Last but not least I have a view CrossWord that ties everything together. When I’m ready I run a query like this one:
SELECT TOP 20 * INTO #temp FROM CrossWord WHERE LastUsed IS NULL AND Category = 'T-SQL' ORDER BY newid()
This gives me a random 20 entries that haven’t been used before for this particular category. The reason I dump them into a temp table is so that I can try them out and if they don’t fit together well for a crossword I can try again. Once I have the final set I add an entry into Uses, get the UseId and do an insert into Clue_Use.
You know, the best part of doing this is the realization that I need to start doing some more crosswords. I haven’t done one in a while and I quite enjoy them. So thanks again for the great topic Todd!