I 100% overlooked the best method. Using queries on the data tables to get lists of weapon types, etc. That way, if say equips made of purple chocolate ever came about, the database could be updated with the new items but the source code would never need to be touched.

No, I need the strings. What I'm going to do to help prevent invalid searches is to get the user to first pick out of weapon, armour, avatar, usable, and material and then depending on what they pick another drop down will appear. If for example they picked weapon, it'd then be mage, gunner, etc, and then the next category would be specific weapon types. If they pick armour though, 2 drops downs would be needed immediately, 1 for slot and 1 for material. The problem arises in trying to figure out the best way to store these strings.

This problem has me very stuck. I've been thinking about it in the back of my mind for hours and hours.

So I need to store all the categories of items (Stuff like armour, belt, gunner, cloth, etc). Only ideas I can think of are hardcoding them and making a database with each category and the category that goes above it. But then that causes problems with the fact that material and slot will both be below armour. And an SQL query won't easily distinguish between them. Both these ideas are considered as bad programming right? -_-

Yah, going to do that. Access *forces* you to have an ID field that is a number. I realised that not only was I making the ID field redundant, I was also probably killing efficiency (2 numbers would be faster to compare than 2 strings, right?). It's also just 1 SQL query to do the linking each way, I dunno what I was thinking before.

I've nearly got my database sorted out now. Though it's still more complex than it should be :/
I hope professor doesn't take marks off me.

Anyway, I mean that like... I have a table called WeaponItem which has all the data related to any given Weapon, and a table called WeaponItemForSale (Now I think about it, I don't know why on earth the word "Item" is in the names, it makes it more verbose than it needs to be). I was going to use the name field in WeaponItem as a primary key and then use it as a foreign key in WeaponItemForSale. The problem with that is that there could be 2 items with the same name, but different stats. When a user puts a weapon up for sale, I was going to just use the name of the weapon to determine which record in WeaponItem I need to reference in the record for the sale in WeaponItemForSale. As you can see, that will cause problems with weapons with the same name. So I wondered if I should be getting that reference using all the data about the item for sale rather than just the name. Understand now? (Though I must admit I'm confusing myself now)

Yeah, I could, but that doesn't change the fact that the name still has to be unique. I'll be identifying which record in WeaponItem goes with a record in WeaponItemSale using the name. I dunno though, maybe that's actually bad database usage. I did some database stuff, but not enough. Any idea if I should be doing what I just described or if I should be linking tables using the ID field that access enforces, and working out which one to link based on *all* the data rather than just the item name?

Ahhhh figure out the previous sales problem. It can all be stored in one table as all the data like stats that vary between item types can just be stored in a single field called notes (Needed to help protect users against stuff like buying a top that gives +1 freeze grenade and then then person dishing up one that gives +1 landrunner claiming that was what the sale was for :P)

Hmmmm okay. I'm just going to manually delete every duplicate and then enforce a rule that no more can be added as I need the name as a primary key.

This assignment is quickly turning into my worst nightmare. Looks like there could be well over 10 tables (So basically the complexity is getting dangerously high) and I'm just not experienced enough at database design to design a schema without serious flaws (Like, keeping records of previous sales and I don't really want them in 5 different tables, and I most certainly do not want them just staying in the item for sale tables as over time that'll make the whole thing go slower and slower and slower until it eventually just grinds to a halt)

Signature

‎
"'How do you fight someone smarter than yourself?' Rand whispered. 'The answer is simple. You make her think that you are sitting down across the table from her, ready to play her game. Then you punch her in the face as hard as you can.'" - The Gathering Storm