How to store 'groups of elements'?

[sunh]

Hi first 

 

I am struggling with a problem and wanted to ask if anybody got an idea how to solve this the best way. It's probably a fundamental question and there are solutions for this.. but unfortunately i'm not able to find them.

 

Let's say you have an object (e.g. room) with properties (e.g. furniture). these properties are of simple and definded structure (say 5 different values like position in the room, color, size, etc.) and there are many of them. Every object has some properties (but not all, like a 'set' just some, various numbers of them). Now you want to store huge amounts of these objects with their properties in a database.

 

The example with the rooms and the furniture is not well choosen because furniture is complex and a room would be considered complete even if one of the furniture pieces is removed.

In the problem to solve the furniture is very simple and the room defined by its content and only existent if complete. A furniture of one room has no meaningful relation to one in another room (data will never be accessed like 'give me a list of all furniture')

 

Well, ususally I would solve this with two tables, rooms and furnitures and another one to connect the ids. (And maybe save the positions in this table too or another)

 

The Problem is now that I dont want to create an own table for the furniture because they are very simple and half their 'value' is their position.. And I dont want to have the objects and their properties in different tables because they are 'fix' and furniture never needs to be accessed without accessing a room. And accessing a room always will be accessing all furniture in there.

 

The solution i am using at the moment is one table 'rooms' with a TEXT field 'properties' filled like a table, e.g.:

1 TABLE PosX=1 PosX=2

2 CHAIR PosX=1 PosX=1

3 LAMP PosX=1 PosX=3

4 BED PosX=1 PosX=2

 

With all disadvantages of loosing memory and query functionality. But if this is the only way, is there at least an approch in optimizing this?

 

I know, you might wonder why i don't use the conventional methods, but one of the problems is the db is really huge (many rooms) and i dont want to do a second query for their properties in a table which contains room*furniture entries...

 

Would rock if anybody had a hint to this  :-\

 

Thanks!