[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.