How to go about modeling the main object of relationship? I'm using MySQL and MyISAM tables.

I have a bunch of objects that I want to group together


1 object belongs to 1 and only 1 group
1 group can have many objects
1 group has 1 required main object that i will choose
manually


So far, I’ve build a model that I believe is the right one.

But I don’t know how to go about modeling the main object in the group. What is the perfect solution?

group_object
----------------
group_object_id (PRIMARY KEY)
name

object
----------------
object_id (PRIMARY KEY)
group_object_id (FOREIGN KEY)
name


UPDATED MY QUESTION.. see bold
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:


Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Apr 30, 2013 : Issues converting MyISAM table to InnoDB (auto column issue)


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&gt; select * from group_object;
+-----------------+--------+
| group_object_id | name   |
+-----------------+--------+
|               1 | GROUP1 |
|               2 | GROUP2 |
|               3 | GROUP3 |
+-----------------+--------+
3 rows in set (0.00 sec)

mysql&gt; 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&gt;


If you run my queries, note how you get the main object from each group

mysql&gt; SELECT group_object_id,MIN(object_id) main_object_id
    -&gt; 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&gt; SELECT DISTINCT B.name GroupName,C.name ObjectName FROM
    -&gt; (SELECT group_object_id,MIN(object_id) main_object_id
    -&gt; FROM object GROUP BY group_object_id) A
    -&gt; INNER JOIN object B ON A.main_object_id = B.object_id
    -&gt; 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&gt;


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 &lt;&gt; @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&gt; 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&gt;


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 !!!