06-13-2008, 04:53 AM
|
#4 (permalink)
|
|
The Contributor
Join Date: Nov 2007
Location: California
Posts: 82
Thanks: 0
|
I'd do it something like this:
Code:
CREATE TABLE monsters (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(120) NOT NULL,
level INT NOT NULL DEFAULT 0,
PRIMARY KEY(id),
INDEX level (level)
);
CREATE TABLE drops (
id INT NOT NULL AUTO_INCREMENT,
monster_id INT NOT NULL,
item_id INT NOT NULL,
PRIMARY KEY(id),
index monster_item (item_id, monster_id)
);
CREATE TABLE items (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(120) NOT NULL,
min_level INT NOT NULL DEFAULT 0,
max_level INT NOT NULL DEFAULT 99,
PRIMARY KEY(id)
);
CREATE TABLE found_items (
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
drop_id INT NOT NULL,
PRIMARY KEY(id),
INDEX user_drop (user_id, drop_id)
);
I added the item levels because I just felt like it ;p
The idea is that the monsters has a 1 to many relationship with drops (1:n). And the relationship between items and drops is many to many (n:n). Ie: Many items can be dropped by many monsters. So your drops table is essentially just a lookup table. Now, you store the drops in the found_items table, where one drop is stored in one row of the found_items (1:1).
This is of course done in MySQL :p |
|
|
|