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
March 26, 2009 at 2:47 pm
GSquared (3/26/2009)
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?
Ah. Hadn't thought of doing it that way. Yeah, that will probably work.
Bruce W Cassidy (3/26/2009)
[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]
heh. Views. Haven't tackled those yet. Once I get the structure figured out and the data imported to where it needs to go, I'll be playing around with views. And probably asking questions.
-- Kit
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply