SQL Question?

[crzyone9584]

Well i sat down and looked at all and decided to go with sql. It seemed a little eaiser because it has native support in the .NET language. So I have two tables set up I'm working on my third. I was wondering if someone could look this over and see if set this up correctly. I chose tinyint because I wont be using a number over 255 on any of the default weapon settings. Then In the character weapons table I'd have the same layout but that table will hold the changes to all the values a player will possibly be able to do via items they attach to the weapon which is anywhere from 1 - 3 items depending on how many slots the item takes up.

Code: Select all

weap_id int NOT NULL,
	name nchar(60) NOT NULL,
	level tinyint NOT NULL,
	type nchar(20) NOT NULL, - this is alike a description
	rarity tinyint NOT NULL,
	p_atk tinyint NOT NULL,
	m_atk tinyint NOT NULL,
	p_def tinyint NOT NULL,
	m_def tinyint NOT NULL,
	fire tinyint NOT NULL,
	water tinyint NOT NULL,
	wind tinyint NOT NULL,
	earth tinyint NOT NULL,
	light tinyint NOT NULL,
        ability tinyint NOT NULL, - this will hold ids for the abilites attached to the weapon that a player has in his/her inventory.
	)  ON [PRIMARY]

[Jackolantern]

It seems to look alright to me. However, you aren't going to get much description in 20 characters. The preceding sentence was over 60 characters long, for example!

[crzyone9584]

Thanks for noticing my mistake. Thanks for letting me know its pretty good design. I was hoping i wasn't that far off from a decent weapon table...

[Jackolantern]

No problem! However, I have to preface any judgement of the design by stating there isn't much we can tell from this. We would have to be intimately familiar with the game's design to really know if this is a well-designed table or not. I don't see any glaring issues, such as data that should be in another table, but it really comes down to if it will suit the game design, and the kinds of data you will need to query from the database. Did you happen to do that prototyping I suggested in the other thread?

[Xaleph]

Just something else, not directly related to SQL, but use normal words for things. So weap_id should be weapon_id. That way it`s better readable to the rest. Some words how can be shortened in the english language as well can remain the same, but for the rest, it`s better and more clear if you use full words.

On topic:
It`s starting to look good, but like Jack said it`s just one table set, so there`s not that much we can say about it.

Well, one thing: you now have 5 fields for the elements ( fire, water, earth et cetera ) What you could do is clear that, and create a new table for elements. That way weapons are not fixed on those 4/5. You can allways add more elements, and more importantly, elements now can have properties. A property for an element could be it`s strength against another element, or its weakness.

So in Weapons you only store the elements_id, that should create more flexibility.

[crzyone9584]

Thanks for the suggestion. I'll look into the elements table and see what I can do with it. Although elements usually only increase slightly when a player attaches attributes/runes to the weapon which will be taken care of in the player weapon table.

[Xaleph]

Well, to create combinations like fire and wind, you could do it a bit different. That way you have a swords table, an elements table and a merged_elements table.

This way you can bind fire to wind, this will be stored in the merged_elements table, it should have a PK obviously, but that`s what you store in the weapons table. Now you can add all kinds of elements to weapons. Lighting, ice, fire, earth, wind flux, darkness, whatever. Now you can also merge wind and ice to form a new element ( blazing ice ). You can also type check the weapons, if a merged set already exists, store that ID in the weapons table. Also, now you can add elements to armor, items whatever. It gives more flexibility. But it all depends on the need. If you know for a fact there are only 4 elements, I doubt it`s worth the overhead, but regardless, a logic seperation is allways a good idea. This way you can modify the elements table and all weapons et cetera are automatically updated. ( well.. not updated, but next time they`ll get the new properties )

[crzyone9584]

Thanks for the suggestion Xaleph. Although that seems right the game I'm making doesn't need to merge any elements together or anything. Although a separate element table sounds better than having to have a armor table, weapon table and so forth with their own element column. Now I just need to figure out how to separate the elements and how to add more than one element id to a weapon/armor. Again you guys are helping me a lot.

[crzyone9584]

I need a little help. I'm trying to seperate the elements but unsure how. each item/weapon will have a different value for the elements and I've been trying to find something on google on how to break the table up so that the elements are in their own table. Any help would be great. Also i can't seem to figure out how to hold 3 id's in one column which will check another table see what added abilities will be added to the item. Any ideas how I go about doing that?

[Xaleph]

The best way to seperate logic is using coupling tables. In the case of elements ( lightning, darkness etc) and weapons You can do it like this:

Code: Select all

table: Weapons
weapon_id PK,
name,
description, 
icon
etc

Table: Elements
element_id PK,
name, 
attributes,
def,
magic, 
atk,
etc

Table: Weapons_Elements
weapon_id,
element_id

It`s important to note that the last table has NO primary keys ( PK ) this is because it`s a collection of ID`s.

This way you can bind more then 1 element to 1 weapon.

It`s like this: weapon can have more then 1 elements, but elements can also be attached to more then 1 weapon. This is called a many-to-many relation. This is a problem, because you don`t want the overhead of creating 3 of the same weapons all just so they have 3 elements, correct?
That`s when you use a coupling table. This only stores the weapon_id and 1 element_id.

So if you have a weapon like this:
id = 1
name = DragonSword

and the following elements:
id = 1
name = fire,

id =2
name = ice,

id = 3
name = wind,

id = 4
name = lightning

If you want to bind fire, wind and lighting to the sword, you create 3 entries in the weapons_elements table. Like this:
weapon_id = 1, elements_id = 1
weapon_id = 1, elements_id = 3
weapon_id = 1, elements_id = 4

So every time you query the sword, you have the sword ID, now all you have to do is query the weapons_elements table where weapon_id = 1.
That way you have all the ID`s of the elements you have bound to the sword.

So now you query all the elements and you have the elements as well.