How to Model Main Object Relationships in MySQL

database-designmyisamMySQL

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

Best Answer

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

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));

MySQL design problem

My question is this, is there a way to dynamically generate Object 3 through MySQL.

When I hear "generate," I tend to interpret that as "insert," but I think you're asking whether MySQL could dynamically select and return the appropriate related records to the application based on certain criteria.

If that's what you're asking, then it seems you have correctly surmised that one sensible way to implement that is indeed with stored procedures...

Also I've read that I shouldn't use stored procedures, and instead do everything with code

Nonsense, I say. Disregard that advice. Stored procedures, used properly, can be used to build an excellent interface between the database tables and the application.

One aspect in particular seems to suit your request:

MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. -- http://dev.mysql.com/doc/refman/5.5/en/stored-routines-syntax.html

Of course, like any other tool, there are inevitably people who come up with ways of misusing them, but this doesn't reflect on the suitability of the tool when properly applied.

But a single call to the database from your application could retrieve the primary object and related data, with stored procedures.

I was thinking of creating a separate table top hold primary keys of related objects

That sounds like you're describing a junction table...

but I'm thinking there is probably a better way of doing that.

If the relationship among objects is many-to-many, then this is the appropriate way of expressing that relationship... but not when it isn't.

Best Answer

SUGGESTION #1 : Use Order of Insertion

Main object_id for every group

Main object for every group by name

Epilogue

SUGGESTION #2 : Use two auto_increments within the same table

Give it a Try !!!

UPDATE 2013-08-28 02:05 EDT

EXAMPLE

YOUR LAST EDIT

Adding a flag

Rearranging to change main object

EXAMPLE

Give it a Try !!!

Related Solutions

Sql-server – With MS SQL Server are the generated constraint names predictable

MySQL design problem

Related Question