Since the tables use the MyISAM storage engine, I have two suggestions.
SUGGESTION #1 : Use Order of Insertion
The simplest way to do that would be arbitrarily insert the main object first for the given group.
Going forward, you would run queries like these:
Main object_id for every group
SELECT group_object_id,MIN(object_id) main_object_id
FROM object GROUP BY group_object_id;
Main object for every group by name
SELECT DISTINCT B.name GroupName,C.name ObjectName FROM
(SELECT group_object_id,MIN(object_id) main_object_id
FROM object GROUP BY group_object_id) A
INNER JOIN object B ON A.main_object_id = B.object_id
INNER JOIN group_object C ON B.group_object_id = C.group_object_id;
Epilogue
To ensure the first rule, 1 object belongs to 1 and only 1 group, add this index
ALTER TABLE object ADD UNIQUE INDEX (group_object_id,object_id);
Having a unique index on those two columns along with object_id definitely prevents insertion of an object_id twice in a group. This unique would speed up GROUP BY queries like the ones I mentioned. It would also speed up the collection of the objects in a group like the following:
SET @given_group_id = 53;
SELECT object_id,name FROM object
WHERE group_object_id = @given_group_id;
and
SET @given_group_id = 53;
SELECT A.name GroupName,GROUP_CONCAT(B.Name) Objects
FROM group_object A INNER JOIN object B
ON A.group_object_id = B.group_object_id
WHERE A.group_object_id = @given_group_id;
SUGGESTION #2 : Use two auto_increments within the same table
All queries in SUGGESTION #1 still apply, but the design is a little different.
Make the design the object table as follows:
object
----------------
object_id
group_object_id
PRIMARY KEY (group_object_id,object_id)
name
MyISAM would handle the incremental values of all object_id per group_object_id. At present, only the MyISAM storage engine has this capability. I have discussed this before:
Give it a Try !!!
UPDATE 2013-08-28 02:05 EDT
Remember I said
The simplest way to do that would be arbitrarily insert the main object first for the given group.
EXAMPLE
Let's say you ran the following to create the tables
use test
drop table if exists group_object;
drop table if exists object;
create table group_object
(
group_object_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (group_object_id)
) ENGINE=MyISAM;
create table object
(
object_id INT NOT NULL AUTO_INCREMENT,
group_object_id INT NOT NULL,
name VARCHAR(20),
PRIMARY KEY (group_object_id,object_id)
) ENGINE=MyISAM;
and load the tables with
INSERT INTO group_object (name) VALUES ('GROUP1'),('GROUP2'),('GROUP3');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP1';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_A'),
(@GivenGroupObjectID,'OBJ_B'),
(@GivenGroupObjectID,'OBJ_C');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP2';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_X'),
(@GivenGroupObjectID,'OBJ_Y'),
(@GivenGroupObjectID,'OBJ_Z');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP3';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_1'),
(@GivenGroupObjectID,'OBJ_2'),
(@GivenGroupObjectID,'OBJ_3');
You will have this data:
mysql> select * from group_object;
+-----------------+--------+
| group_object_id | name |
+-----------------+--------+
| 1 | GROUP1 |
| 2 | GROUP2 |
| 3 | GROUP3 |
+-----------------+--------+
3 rows in set (0.00 sec)
mysql> select * from object;
+-----------+-----------------+-------+
| object_id | group_object_id | name |
+-----------+-----------------+-------+
| 1 | 1 | OBJ_A |
| 2 | 1 | OBJ_B |
| 3 | 1 | OBJ_C |
| 1 | 2 | OBJ_X |
| 2 | 2 | OBJ_Y |
| 3 | 2 | OBJ_Z |
| 1 | 3 | OBJ_1 |
| 2 | 3 | OBJ_2 |
| 3 | 3 | OBJ_3 |
+-----------+-----------------+-------+
9 rows in set (0.00 sec)
mysql>
If you run my queries, note how you get the main object from each group
mysql> SELECT group_object_id,MIN(object_id) main_object_id
-> FROM object GROUP BY group_object_id;
+-----------------+----------------+
| group_object_id | main_object_id |
+-----------------+----------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+-----------------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT DISTINCT B.name GroupName,C.name ObjectName FROM
-> (SELECT group_object_id,MIN(object_id) main_object_id
-> FROM object GROUP BY group_object_id) A
-> INNER JOIN object B ON A.main_object_id = B.object_id
-> INNER JOIN group_object C ON B.group_object_id = C.group_object_id;
+-----------+------------+
| GroupName | ObjectName |
+-----------+------------+
| OBJ_A | GROUP1 |
| OBJ_X | GROUP2 |
| OBJ_1 | GROUP3 |
+-----------+------------+
3 rows in set (0.00 sec)
mysql>
Note how the main inserted object for each group has the minimum object_id for that group.
When you apply the rule I first mentioned, simply look for the minimum object_id for any given group_object_id from the object table.
YOUR LAST EDIT
Adding a flag
If you already have data in the object table and want to assign a random object as the main object, you could add a flag called main as INT NOT NULL DEFAULT 0 to the object table. Assuming responsibility, you would have to check to see, upon insertion, if a group already has a main object. If so, you would update the main flag of the previous main object to 0. Then, you could update another object's main to 1.
Rearranging to change main object
If you try adhering to my rule, first insertion is main, here is what you can do so that you can choose which object becomes first in the group:
EXAMPLE
Let's say from my example, that you want take OBJ_Z in GROUP2 and make it the new main object. You would do this:
SELECT group_object_id,object_id
INTO @GivenGroupID,@GivenObjectID
FROM object WHERE name = 'OBJ_Z';
DROP TABLE IF EXISTS objectnew;
CREATE TABLE objectnew LIKE object;
--
--
INSERT INTO objectnew (group_object_id,name)
SELECT @GivenGroupID,name FROM object
WHERE group_object_id = @GivenGroupID
AND object_id = @GivenObjectID;
INSERT INTO objectnew (group_object_id,name)
SELECT @GivenGroupID,name FROM object
WHERE group_object_id = @GivenGroupID
AND object_id <> @GivenObjectID;
--
--
DELETE FROM object WHERE group_object_id = @GivenGroupID;
INSERT INTO object SELECT * FROM objectnew ORDER BY object_id;
DROP TABLE IF EXISTS objectnew;
SELECT * FROM object ORDER BY group_object_id,object_id;
If you run this code, here is the new output
mysql> SELECT * FROM object ORDER BY group_object_id,object_id;
+-----------+-----------------+-------+
| object_id | group_object_id | name |
+-----------+-----------------+-------+
| 1 | 1 | OBJ_A |
| 2 | 1 | OBJ_B |
| 3 | 1 | OBJ_C |
| 1 | 2 | OBJ_Z |
| 2 | 2 | OBJ_X |
| 3 | 2 | OBJ_Y |
| 1 | 3 | OBJ_1 |
| 2 | 3 | OBJ_2 |
| 3 | 3 | OBJ_3 |
+-----------+-----------------+-------+
9 rows in set (0.00 sec)
mysql>
Note that OBJ_Z is the main object because it is the first in the group. Simply query for the name of the object you want to be main in the first line and then run all the code. I have tried repeatedly, and it works.
Give it a Try !!!
My first IT job was in this area - basically I was involved in a "fiddle" on the part of my employer to land a big contract which involved convincing the client that we had a functioning and "intelligent" order picking system. It involved alcholic beverages and there are many complex rules about these (tax reasons).
Anyway, I just wish that Open Source had been as prevalent then as it is now - because there appear to be a plethora of high-quality projects out there in this area. A quick Google landed me here and here - search for the word "pick" on these pages.
If I were you, what I would do is examine these (and others...) packages and see which ones correspond most closely to your needs - download and install them and then check out the underlying schemas and see which bits you can borrow for your project. Or you may just decide to implement one of the systems that's already out there?
What I do know is that the logistics behind these problems are very complex (operations research, tricky mathematics &c.), so you might be able to take some of the pain out of your project by "standing on the shoulders of giants"!
[EDIT]
You might like to check out this and its parent site- it specifically mentions bin-packing and employee rostering, so it might be of interest. I remember this from studying operations research. If you work in Java, it could be of interest - although the principles obviously apply to all languages.
Best Answer
Great, the part/whole modelling problems.
Here is how LedgerSMB does it. We are likely to change this at some point but this part works well enough.
Basically we have two tables, called parts and assembly. The exact names/layout will change over time but this basic concept will probably stay the same.
In the parts table, we have parts (goods in inventory), services, and assemblies (as you put it, sets). In the assembly table we store the assembly to connect with, the part or assembly that is a part of it, and the number required. This allows sets to be parts of other sets recursively. This effectively creates a components tree in assembly and a sales component tree in parts.
It works well enough, especially when done on a database which supports WITH RECURSIVE common table expressions (i.e. not MySQL...)
Of course then you have a table or two to manage inventory movements and this would handle the sets and their deconstruction according to your business rules.