[SOLVED]Derp moment with mysql DB design?

[LittleSoul]

EDIT: For anyone else needing help with this craziness, it really helped to look at this website:
http://office.microsoft.com/en-us/acces ... 24247.aspx
I know it's for Access, but that doesnt matter. What matters is the clear definition and examples of many to many relationships!


Sigh. Alright, so I'm designing my database, and I'm following along the rules of normalization; but I'm stuck

While doing this, I realized that I have a situation where a field seems to need more than one value within it.
And I know that's very wrong, and it'll be a big pain later if I do it that way.
And I've got several tables that have the exact same problem.

What I've got is:

Clothing (Linked to MaterialDegen, Tools, Resources)
-Name
-Primary Key (Clothing)
-Degen FKey
-ReqResources
-ReqTools

I have tables like this, where they have required items and resources in order to be made.
Like for tools, if it's a silk dress, you have to have scissors, a sewing needle, and a loom.

I was told to use a junction table, which I know what that is, but I'm not sure I'd know how to apply that here.
I think maybe I'm having a brain dead moment. It's a many-to-many relationship, for sure.
Many tools are used to make items, and almost all items require more than one tool..

So how the heck would I make this junction table without using more than one value, or having an obscenely large table with lots of redundant repetition?

Can anyone help this noob?

[Chris]

Couldn't fully understand the situation, here's what I think you're on about.

For example when making an item you need to run a check on what other items a person may need, well it's quite simple, there are several ways of storing lists of things. I'd advise making a new table, example:

Required crafting items table:
id,
required_item_id,
required_item_amount,
production_item_id

Store a new row for every item needed for what item you will need to produce.

Crafting script example:

Code: Select all

$requiredItemsQuery = mysql_query("SELECT * FROM `required_crafting_items` WHERE `production_item_id` = 50"); // assuming we're making item #50
$requiredItemsArray = array();

while($requiredItem = mysql_fetch_assoc($requiredItemsQuery) )
{
    $requiredItemsArray[] = $requiredItem;
}

$canMakeItem = true;
foreach( $requiredItemsArray as $requiredItem )
{
    if( !$CurrenPlayer->HasEnoughOfItem($requiredItem['required_item_id'], $requiredItem['required_item_amount']) )
    {
        $canMakeItem = false;
    }
}

if( $canMakeItem )
{
    $entryId = MakeItem(50);
    $CurrentPlayer->GiveItem($entryId);
}
 

[LittleSoul]

So if I had example tables, that go with the way the game is supposed to run, would it look sort of like this?

Required Tool Table
key
required_tool_Fkey, //there will never be more than one of the same type of tool needed
production_item_Fkey

Required Resource Table
key
required_resource_id
required_resource_amount
----------------------------------

EXAMPLE

Required Tool Table
key 123
required_tool_id: 394 (Sewing Needle)
production_item_FKey:456 (Silk Dress)

key 456
required_tool_id: 395 (Scissors)
production_item_FKey:456 (Silk Dress)

key 789
required_tool_id: 396 (Loom)
production_item_FKey:456 (Silk Dress)

Required Resource Table
key 101
required_resource_id: 394 (Silk)
required_resource_amount: 4
production_item_FKey:456 (Silk Dress)


Because these are the tables in notepad that I've got so far; and the way it's looking.. it might be a mess lol. There are so many groups of items that are related to tools and resources. I'm obviously not done with the tables yet, so if any of them seem weird or dont make sense, or are missing some things. I know. It's just not done~

Accounts (Linked to Characters)
-PrimaryKey
-Username
-Password
-Email

Characters (linked to accounts)
-Primary (Character) Key
-Name
-Age
-Description
-Location (insert Fkey)
-ImageURL Key (Insert Fkey)
-Account (Insert FKey)
-Race (insert FKey)

Images
-Primary Key
-Image URL

>>>Races (linked to skills?, and Characters)
-Primary(Race) Key
-Name
-Lifespan
-Benefits
-Description
-Fly (yes, or no)
-Swim (yes, or no)

Terrain
-Primary Key (XY)
-Type
-Description
-Coordinate X
-Coordinate Y

>>>>Animals (linked to terrain, taming skill, combat hunt,

and food resources)
-Primary(Animal) Key
-Name
-Description
-Terrain Keys
-Tame (yes, or no)
-Defense
-UnarmedAttack
-Reaction
-Drop

MaterialDegen
-Primary (material key)
-MaterialType
-Degen

Required Crafting Items Table
-Primary Key
-required_item_id,
-required_item_amount,
-production_item_id

>>>FoodResources (linked to terrain, MaterialDegen, and

animals)
-Name
-Primary Key (FoodResources)
-Type (Fish, AnimalProduct, ForagedFood, FarmVeggies)
-Degen (put in key)
-ReqTool (insert Fkey)
-HungerPoints
-Seeds? (Yes or no)

CookedFood (linked to MaterialDegen, Tools, Resources)
-Name
-Degen (put in key)
-Primary Key (CookedFood)
-ReqResources
-ReqTools
-HungerPoints

Clothing (Linked to MaterialDegen, Tools, Resources)
-Name
-Degen (put in key)
-Primary Key (Clothing)
-ReqResources
-ReqTools

Buildings (Linked to MaterialDegen, Tools, Resources)
-Name
-Primary Key
-UnitsCanHold
-Rooms
-Degen (put in Fkey)
-ReqResources
-ReqTools

Jewelry (Linked to MaterialDegen, Tools, Resources)
-Name
-Degen (put in key)
-Primary Key (Jewelry)
-ReqResources
-ReqTools

Tools (linked to MaterialDegen, Resources,)
-Name
-Degen (put in key)
-Primary Key
-ReqResources
-ReqTool

Weapons (linked to MaterialDegen, Tools, Resources)
-Name
-Degen (put in key)
-Primary Key (Weapons)
-ReqResources
-ReqTool
-Attack

Armor (linked to Material Degen, Tools, Resources)
-Name
-Degen (put in key)
-Primary Key (Armor)
-ReqResources
-ReqTool
-Defense

RegResources
-Name
-Primary Key (RegResources)
-Degen (foreign, put in key)
-Description
-Terrain (foreign, instert key)
-ReqTools (foreign, instert key)

I'm starting to feel overwhelmed! haha.

[Xaleph]

First things first, lose the foreign keys, MySQL doesn`t really work with them ( they don`t mean anything, saves you some time when implementing)

Second, junction tables is what you need. As you already said, you have n-n relations, many to many. That allways means a junction table. Funny story btw, i never knew the English word for it hehe.. I call them linked tables or coupling tables. Anyway.. Many to many can be transfered to many to one, one to many. Which solves all your problems.

Say you have tools and items, different tools can make different items and different items can be crafted with different tools, ( n-n )

Tools > - n - < items

Solved by using:

Tools > 1 - < ToolsItems > 1 - < Items

so :

T TI I
23 > 23-198 -> 198
23 > 23 - 467 -> 467
12 > 12 - 198 -> 198

As you can see, for tool with ID 23 you can store multiple items to it, all in a new record in your junction table. Works both ways as well. It`s a simple example, but i think it`s just what you need. Hope it helps.

[LittleSoul]

Right~! That's what I attempted to do in my other follow up post example. The one thats colorful. Did I sort of do it right?
Sorry I know I'm such a newbie, but I really do appreciate the help guys. <3

Also, I thought foreign keys were what linked the tables, and created those relationships?
If I dont have those then I dont have any relationships :[

[Jackolantern]

Ok, this takes a bit of clarification

"Foreign key" can mean two different things: a similar column in two different tables that form a relationship between tables, or a feature of the database management system (RDBMS) that enforces that relationship. Every relational database has the former, where you can put the same column in two tables (typically the index of the originating table, and a reference for the other table). Without that ability, relational databases fall apart.

Now in MySQL, everyone says "MySQL does not support foreign keys". However, they mean the 2nd meaning. Foreign keys are not recognized as such in MySQL. They are just another column, so you have to enforce the fact that it is a foreign key yourself, in your application logic code. In many other RDBMS, such as SQL Server, you can actually set a field to be a foreign key from another table and the database itself will ensure this is correct.

It isn't a big deal that MySQL does not support them. It is just a tiny bit more spot-checking you will have to do yourself. In large databases, enforcing foreign keys can be a big performance hit, and it does slow down Microsoft SQL Server once a database gets huge (among a few other reasons).

Also, it is a best practice in MySQL, to make your foreign key name in the table referencing it the same as in the originating table. For example, say you have a `players` table that is indexed with a primary key column called `playerId`. Then you want to reference that table in another table called `playerCities`, which holds a listing of player-created and player-owned cities. Each one of those player-owned cities in that table are going to have their own `cityId`, but each record also needs to refer to what player owns the city. So you would just add another column called `playerId` to the `playerCities` table. If your foreign key columns are descriptive, and clearly state what table's ID they represent, they are going to be self-documenting so you will not need to put anything in them to say they are foreign keys.

The reason you should use the same name is to ease query writing:

Code: Select all

SELECT *
FROM players, playerCities
WHERE players.playerId = playerCities.playerId;

You can see this makes the query much more obvious that we are connecting the players table to the playerCities table. That can keep things much more manageable once you get many, many different tables in your database. All you have to remember is that you take the name of the table you want, slap "Id" at the end of the name, and there is your ID. And then you can would also know that all foreign key columns for that table have the same name as well

[LittleSoul]

Thank you Jack! I was pretty confused for a minute there, but you cleared that right up!
I'm still pretty frustrated with figuring out this junction table. It was easy at first but now there's relationships everywhere and I'm getting overwhelmed. Lol.

But I'll get it. Probably just start with a few basic finished tables, test them out, get an idea of what I'll be doing in my logic
and then move on to these many tables and their crazy relations. I didn't realize crafting systems could be so intimidating!

Thank you (:

[Winawer]

MySQL supports foreign keys if you use the InnoDB storage engine.

[Jackolantern]

True, but for non-transactional usage, you should stick to MyISAM. MyISAM is orders of magnitude faster than InnoDB in MySQL, and InnoDB is really just there for databases that need transactions. A foreign key feature is not a good reason to use InnoDB, since it is so easy to enforce in code.

As far as the many-to-many intermediate table goes, you could check out this tutorial here. It is a few years old, but the information in it has not really aged. It should hopefully clear up the mysterious intermediate table

[LittleSoul]

Thanks a lot (: I think I figured it out. As you can see, I split things up into smaller tables, and actually ended up putting a couple into one table. Then I linked them appropriately. If you're wondering what this is for (to see the reasoning behind these tables), it's a text based browser game (; probably wasn't hard to figure that out haha.
Let me know how it looks!

EDIT: For anyone else needing help with this craziness, it really helped to look at this website: http://office.microsoft.com/en-us/acces ... 24247.aspx

I know it's for Access, but that doesnt matter. What matters is the clear definition and examples of many to many relationships!

Resource_Degeneration
-primary_key
-resource_id
-degen

Item_Degeneration
-primary_key
-item_id
-degen

Resources
-name
-resource_degen_id
-description
-terrain_type_id
-required_tools

Animals
-name
-description
-terrain_type_id
-tame (yes or no)
-defense
-unarmed_attack
-reaction
-animal_resources_id

Animal_Resources
-name
-description
-animal_id

Plant_Resources
-name
-description
-degen
-required_tools_id
-hunger_points
-seeds? (yes or no)

Tools
-name
-degeneration
-description
-required_resources
-required_tools

Req_Plant_Resources
cooked_food_id
food_resource_id
food_resource_amount

Req_Resources
item_id
resource_id
resource_amount

Req_Tools
item_id
tool_id

Items
(includes clothing, and jewelry)
-item_id
-degeneration
-description
-required_resources_id
-required_tools_id

Cooked_Food
-name
-degen
-required_animal_resources_id
-required_plant_resources_id
-required_tools_id
-hunger_points

Buildings
-name
-units_can_hold
-rooms
-degen
-req_resources_id
-req_tools_id

Armor
-name
-degen
-defense
-req_resources_id
-req_tools_id

Weapons
-name
-degen
-weapon_attack
-req_resources_id
-req_tools_id