Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Could you choose to insert the main object first for any group ? –  RolandoMySQLDBA Aug 28 '13 at 5:07
    
most of the time, i would end up choosing manually the main object. So, the answer is no, it could happen but it's not the rule. –  Marco Aug 28 '13 at 7:06
1  
This problem looks similar: How to have a one-to-many relationship with a privileged child? I wonder though how you will implement foreign key constraints with MyISAM tables. –  ypercube Aug 28 '13 at 7:25

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
thanks for the detailed answer. I believe i haven't explain correctly my main issue, which is that i need to insert the main object id in the table, not just querying the main object. I will update my question –  Marco Aug 28 '13 at 4:58
    
thanks again. since i have to manually select the main object upon analysis, i believe the 'adding a flag' solution is the one for what i need to do. Thanks –  Marco Aug 28 '13 at 7:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.