March 9, 2009 at 3:08 pm
I’m new to SQL. My current job duties include running some scripts and stored procedures in Management Studio that process lists of data provided by our clients. I understand what I’m doing (and not in any danger of blowing up the server), but not all of what’s happening in the procedures I’m running. So, I figured it was time to learn more about SQL and T-SQL and the best way to do that is to build a database of my own and ask questions, make mistakes and hopefully learn from them all without screwing up a production database at work (cause that would be bad).
So, as a personal database, I decided to make a database of information for an online game I play (City of Heroes if anyone is interested). The information in question is from their Invention system (creating enhancements for player powers) and it basically breaks down into two different categories, Salvage (the items you collect) and Recipes (the recipe together with the Salvage will give you the enhancement).
I’ve figured out how to store the Salvage. I have one main table of the salvage items with foreign keys to three other tables for different properties that the salvage has. For instance, there are three level tiers of salvage so one of the Salvage table foreign keys points to a table that lists the three tiers. I've uploaded a database diagram of those tables for those interested.
But, when it comes to the Recipes, I’m not sure what the best way to proceed is. Each recipe uses two to four (or maybe five, I haven’t verified that one yet) pieces of salvage. If it uses two pieces of salvage, the two pieces are not the same (but are of the same tier level). If there are three or more pieces, one of the pieces can be duplicated.
For Example:
Recipe 1
Salvage A Tier 1
Salvage B Tier 1
Recipe 2
Salvage C Tier 2
Salvage C Tier 2
Salvage D Tier 2
Now, the question I have is how to store this information?
Should I have a recipe table that has the name of the recipe and then a column for each ingredient, having the salvage listed twice as necessary?
Name, SalvagePiece1, SalvagePiece2, SalvagePiece3, SalvagePiece4
Recipe 2, SalvageC, SalvageC, SalvageD, null
Or should I have a table with the recipes in it and then a join table that just references the recipe and the salvage.
Recipes_Salvage
RecipeID, SalvageID
I guess in order to handle the recipes that have two of the same piece of salvage I would have to put in a primary key so I would have this:
ID, RecipeID, SalvageID
1, 2, C
2, 2, C
3, 2, D
Doing it this way, looks like I have to get real familiar with INNER JOIN and the like to get anything useful out of the database, not that I mind.
Is the join table the way to go? Or are there a few other things I should consider before making the decision?
I also have a question of what front end can I put on this database and where to start on that. A few years ago, I would have just started with an Access front end, but that's not the solution nowadays. I know from listening to people that a .NET application is probably the way to go, but I have no idea where to even start concerning .NET. Pointers on front end options and where to start would also be appreciated. 🙂
-- Kit
March 9, 2009 at 3:13 pm
[font="Verdana"]Why not use Access as a front end? If it works, don't fix it...
One thought: your data already has constraints applied to it by the application (City of Heroes -- I'm a WoW man myself.) So you don't need to enforce the constraints at the database level. That may make it a litle easier.
So in that case, all you need is the salvage and the count together on one row. In most cases, the count will be 1, so set that as the default.
[/font]
March 9, 2009 at 3:24 pm
The main reason not to use Access is that I don't have a copy of it and I'm cheap. I'm using SQL Server Express (although I suppose I could spring for a developer copy).
There are a bunch of other properties that the recipes have, like level ranges and just how much they add to the power in question. I guess the question I have is should I have one big table with all the recipe information in it (what salvage it takes, what level ranges it covers, what percentages it adds)? Or split it out into separate tables due to the properties as there are recipes that have the same percentages but are used for different powers?
-- Kit
March 9, 2009 at 3:33 pm
[font="Verdana"]Not having a copy of Access could rule out using it, I agree. What do you have a copy of? I mean, there's no point recommending C# or VB.Net unless you have Visual Studio.
The general rule with normalisation is this: split it apart to remove duplication. If all of your attributes relate directly to the recipe then keep with with the recipe. If the attributes relate directly to the component of the recipe then keep with the component.
So in your example, the level of a recipe would be kept against the recipe. You might also choose to keep it against a component, as the component might have a level requirement also (and that could be different to that of the recipe itself.)
[/font]
March 10, 2009 at 6:07 am
I would recommend using the Recipe_Salvage table. This is normalization. If you try to put it all in the recipes table then you could potentially have to add columns as the recipes get more complex.
As far as front end, depending on your background, VB.NET wouldn't be hard to learn, not would C# if you have and C or Java experience. Since MS does provide the free Express editions of C# and VB I'd get them both and see which one I like.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2009 at 7:28 am
Bruce, Jack,
Cool. I'll give VB.NET and C# a try. Hope to play with it this weekend.
As for the table structure, I definitely want to keep it as normalized as possible, as that's the whole reason behind a relational database from everything I've been able to figure out. I guess something else to keep in mind in figuring out normalization is to make sure if the data changes I'm only updating it in one place, right? So if I have a property that I have to update, if it applies to both recipes AND salvage, it should be in its own table so I'm not updating two tables.
I know these seem like newbish questions, but I do want to understand the basics so my first database isn't too much of a disaster. 😛 And thanks very much for your input so far. 🙂
-- Kit
March 10, 2009 at 7:36 am
Kit G (3/10/2009)
As for the table structure, I definitely want to keep it as normalized as possible, as that's the whole reason behind a relational database from everything I've been able to figure out. I guess something else to keep in mind in figuring out normalization is to make sure if the data changes I'm only updating it in one place, right? So if I have a property that I have to update, if it applies to both recipes AND salvage, it should be in its own table so I'm not updating two tables.
That sounds about right to me.
Have Fun!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2009 at 9:33 am
Oh, I intend to. 😀 Organizing the data for my Recipe_Salvage table is going to be fun.
One of the things I want to do is include the icons used for the salvage and recipes. I know that SQL can handle pictures as objects or something, but in trying to figure out how to do this, I got lost in BOL, probably not starting in the right spot or something.
Is there a simpler reference on how to handle images, or references to images in the database? Or can you give me the right spot to start in BOL?
Thanks a bunch.
-- Kit
March 10, 2009 at 11:32 am
Is there a simpler reference on how to handle images, or references to images in the database? Or can you give me the right spot to start in BOL?
SQL Server 2005 Books Online (September 2007)
Using text and image Data
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/996f2f14-51dd-4e92-a422-d5ab417f2d39.htm
March 10, 2009 at 1:41 pm
Thanks. I'll check it out. And post again if I have questions. 🙂
-- Kit
March 16, 2009 at 8:56 am
Okay, got a question on the normalization of my data. The way I had one of my tables designed turns out to be wrong and I’m trying to fix it. (And my apologies if the explanation is a bit long.)
With this database, I want to be able to query the salvage and find out what recipes I can build with it and also to be able to query the recipes and found out what salvage I need to make the recipe.
dbo.Recipes looks like this: (this is the table that needs fixing)
CREATE TABLE [dbo].[Recipes](
[RecipeID] [int] IDENTITY(1,1) NOT NULL, -- Primary Key
[RecipeName] [varchar](50) NULL, -- Name
[RecipeDescription] [varchar](300) NULL, -- Description
[RangeStart] [int] NULL, --Lowest level of recipe available
[RangeEnd] [int] NULL, -- Highest level of recipe Available
[CommonSet] [bit] NULL, -- Common Recipe? or Set Recipe (Uncommon)?
[EnhancementType] [int] NULL, -- What the result of the recipe enhances
[RecipeTier] [int] NULL, -- Recipe Tier / Salvage Tier used
[RecipePool] [varchar](30) NULL) -- What group of critter drops it
RangeStart and RangeEnd are the character levels the recipe is good for. This is the data I need to pull out of the Recipe table so I can normalize it. For instance, the Accuracy Recipe can be gotten at level 10, 15, 20, 25, 30, 35, 40, 45, 50. Level 10 through 25 uses the first tier of salvage, level 30 through 40 is the second tier and level 45 through 50 is the third tier.
I currently have the recipe name in the table three times, one for each tier of salvage.
RecipeName, RangeStart, RangeEnd
Accuracy, 10, 25 -- Uses Low Level Salvage
Accuracy, 30, 40 --Uses Mid Level Salvage
Accuracy, 45, 50 -- Uses High Level Salvage
Common Recipes are only available on increments of 5. The low-level Accuracy recipe is only available as a level 10, 15, 20, or 25 recipe. Uncommon recipes (or Sets) are available in increments of 1. So an low-level uncommon recipe can be available as a level 10, 11, 12, etc., up to level 25 recipe.
The level splits have an overall split like this:
10-25 – Low Level
26-40 – Mid Level
41-50 – High Level
But, depending on the set, you can get the splits listed below. There are a few sets that are available from level 10 to level 50.
(Data for the RecipeRange table that pulls out the range data from the Recipes Table)
ID, Range, Description
1, 10-25, Low-Level
2, 15-25, Low-Level
3, 20-25, Low-Level
4, 25-25, Low-Level
5, 26-30, Mid-Level
6, 26-35, Mid-Level
7, 26-40, Mid-Level
8, 41-50, High-Level
So, here’s the question. It seems like the best way to pull the level data out and normalize it is to have a Level table, a RecipeRange table and join those two tables to the Recipes table.
That makes my join table look something like this:
Recipe, RecipeRange, Level
2, 2, null (for the range an uncommon recipe covers referencing the RecipeLevel Data above)
2, 5, null
3, null, 10 (for the single level(s) a common recipe covers)
3, null, 15
3, null, 20
3, null, 25
I think the above join table works the best. Is there any reason NOT to implement it this way? I have a join table joining basically three tables, Recipes, RecipeRange and Level. Is this a no-no in database design? Does the above work at normalizing the data? I don’t really want to have 41 entries for an uncommon recipe that goes from level 10 to 50 if I just had a join table between Recipes and Level.
-- Kit
March 26, 2009 at 6:34 am
Kit,
I don't know if you have finished this yet, but I have been looking at it since you posted it and trying to make sure I understand what you need to accomplish with the design and I'm not sure that I do.
I do think that the design you have proposed would lead to a more normalized database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 26, 2009 at 7:27 am
Hi Jack,
Well, my first question is whether or not it is a no-no to have a table joining three tables. I know if you want the one-to-many or many-to-many relationships that you create a table to join them using the primary or foreign keys or whatever else you need to use to connect the two tables.
But is it okay to do it with three tables? Seems like you could but I don't know if there are performance issues down the road that make doing that a bad idea.
To explain a bit further on what I'm trying to do: Each Recipe with the proper Salvage combines to produce an Enhancement. The Recipe has a level attached to it stating what level of Enhancement it will produce. So, the level is a property of the Recipe and since the same level can be a property of different recipes, it needs to get pulled out to its own table per normalization. That's the realization I had when I started putting data into my tables.
However, since Recipe A from Level 10 to 25 uses the same Salvage (so Recipe A of level 14 uses the same Salvage as Recipe A of level 22) I figured it would be easier to associate Recipe A with a level range instead of having an entry in the Join table for each individual level. One entry as opposed to 16 entries.
But there are some recipes that are only available at certain levels (level 10, 15, etc.) and I need to distinguish those from a range. Hence my idea for the above join table (Recipes_Level) to join the Recipe with the Level Range (when appropriate) or the specific Level.
And actually, looking again at my example, it doesn't look like I really need a separate Level table because what I'd be putting into the Recipes_Level join table in the Level Column would be the level of the Recipe. Kinda silly to have a table with the levels.
Level Table
ID Level
1, 1
2, 2
... ...
10 10
And then to go and put that into the Level column.
Recipe Range Level
3, Null, 10
It's the exact same data so there really isn't an advantage to doing that. It's not like I'm saving on storage space or anything.
But, for further development on my database, is it okay to have a table join more than two other tables?
-- Kit
March 26, 2009 at 1:07 pm
[font="Verdana"]So long as you are happy writing the SQL, then joining the additional tables should be fine.
You can create views that already have the joins done to simplify things if you wish.
[/font]
March 26, 2009 at 1:54 pm
You could put the level range and level step in the join table.
Thus, if the recipe goes from level 10 to level 25, but only has 10, 15, 20, 25, you'd put range 10-25, step 5. If it goes 10 to 25, but has versions for every level in that range, you'd put step 1.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply