What you are doing here is called specialization.
You can look at this answer to a similar question at stackoverflow which will point you in the right direction. I've implemented a slightly modified version with success.
*An other option (that I do not advise!!) is the use of EAV. *
Edit: I've created an example for your situation. (Don't mind the sample data ;) )
CREATE TABLE EventType (
EventTypeID INT NOT NULL PRIMARY KEY,
EventType VARCHAR(200) NOT NULL
);
INSERT INTO EventType (EventTypeID, EventType) VALUES (1, 'Play Rockband'),(2, 'Play Call of Duty');
CREATE TABLE Event (
EventID INT NOT NULL PRIMARY KEY,
EventTypeID INT NOT NULL,
Event VARCHAR(200) NOT NULL,
CONSTRAINT UQ_Event UNIQUE (EventID, EventTypeID),
CONSTRAINT FK_Event_EventType FOREIGN KEY (EventTypeID) REFERENCES EventType(EventTypeID)
);
INSERT INTO Event (EventID, EventTypeID, Event) VALUES (1, 1, 'Play the bass'),(2, 2, 'Sniper!');
CREATE TABLE SpecialEvent1 (
EventID INT NOT NULL PRIMARY KEY,
EventTypeID INT NOT NULL,
Extrafield VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent1 */
CONSTRAINT CHK_EventTypeID_1 CHECK (EventTypeID = 1),
/* Check if event exists */
CONSTRAINT FK_SpecialEvent1_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);
INSERT INTO SpecialEvent1 (EventID, EventTypeID, Extrafield) VALUES (1, 1, 'Expert');
CREATE TABLE SpecialEvent2 (
EventID INT NOT NULL PRIMARY KEY,
EventTypeID INT NOT NULL,
Extrafield2 VARCHAR(200) NOT NULL,
Extrafield3 VARCHAR(200) NOT NULL,
/* Restrict to only insert SpecialEvent2 */
CONSTRAINT CHK_EventTypeID_2 CHECK (EventTypeID = 2),
/* Check if event exists */
CONSTRAINT FK_SpecialEvent2_Event FOREIGN KEY (EventID, EventTypeID) REFERENCES Event(EventID, EventTypeID)
);
INSERT INTO SpecialEvent2 (EventID, EventTypeID, Extrafield2, Extrafield3) VALUES (2, 2, 'Character X', 'Team Z');
You can query the events like this:
SELECT EventType.Eventtype AS [Type of event], Event.Event, SpecialEvent1.Extrafield AS Difficulty
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent1 ON Event.EventID = SpecialEvent1.EventID AND Event.EventTypeID = SpecialEvent1.EventTypeID;
SELECT EventType.Eventtype AS [Type of event], Event.Event [Play As], SpecialEvent2.Extrafield2 AS Character, SpecialEvent2.Extrafield3 AS Team
FROM Event
INNER JOIN EventType ON Event.EventTypeID = EventType.EventTypeID
INNER JOIN SpecialEvent2 ON Event.EventID = SpecialEvent2.EventID AND Event.EventTypeID = SpecialEvent2.EventTypeID;
SQL Fiddle: http://sqlfiddle.com/#!6/d1911/1/0
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
Sounds like a "many:many" relationship.
See my tips on writing an optimal many:many table.
I assume you have a
PRIMARY KEY
for the Event table; perhaps the event name? It should be used in the many:many table.