[dusk]

Hello, I'm rewritting a system that lets people buy furniture for their homes. My current problem is that I can't think of a nice way to load furniture texture data.

My furniture table if needed:

Код:

CREATE TABLE IF NOT EXISTS house_furniture (
	id INT NOT NULL AUTO_INCREMENT,
	house_id INT NOT NULL,
	furniture_id INT NOT NULL,
	name VARCHAR(64) NULL,
	pos_x FLOAT NOT NULL,
	pos_y FLOAT NOT NULL,
	pos_z FLOAT NOT NULL,
	rot_x FLOAT NOT NULL,
	rot_y FLOAT NOT NULL,
	rot_z FLOAT NOT NULL,
	PRIMARY KEY(id),
	INDEX(house_id),
	INDEX(furniture_id),
	INDEX(texture_id)
) ENGINE=INNODB;

I do have some ideas.

First one. Creating a separate table referencing the furniture id.

Disadvatanges
No nice query to load them(not that i can think of atleast). I considered various union queries but none of them gave me a convenient way.
Advantages
Good readability
Possibility to use sscanf on resultset


Second idea.
One text column with a specific format and a separator. For example:

Код:

slot-txd_name-texture_name-color/slot-txd_name-texture_name-color/ and so on

Disadvantages
Terrible readability
AFAIK it's bad practice(although I was advised for this method)
Wouldn't be able to use sscanf(due to unknown amount of texture slots used)



These ideas are only ideas. Please do share your opinions and don't hesitate suggesting something different.

[dusk]

bump

[Vince]

Second method completely defeats the purpose of relational databases.

It is true that it is rather annoying if each house can have multiple pieces of furniture and each piece of furniture can have multiple textures. You will need to join the tables regardless. In the loading code, you may check if the current furniture id equals the last one that was loaded. If it isn't, create the object. Then apply the textures.

[Kar]

use a relational database linking the houseIDs.

create a trigger to remove all furniture if a player chooses the option to buy the house with the furniture, or nah.

no problems, no issues.

[PaulDinam]

This is the layout of my table, I have five material indexes for each furniture.
I use sscanf to separate with "|" delimiter and then set the object material according to the created dynamic object.
When saving the furniture I just retrieve the object material using GetDynamicObjectMaterial text and then format the first five slots like this "xx|xx|xx|xx", for example: http://i.imgur.com/dkMzwau.png

model|txdname|texturename|color, 0 = default

[dusk]

Thanks a lot everyone.