MySQL design problem

Question

I have nearly no experience in database design, so forgive me if I'm asking something super nooby and ridiculous :)

I have an item I need to create. This item will be constructed from 3-4 types of objects, with multiple object instances. Basically as per below:

• Object 1: only 1 instance per item
• Object 2: extends Object 1 and shares a foreign key, may have multiple instances, one instance mandatory
• Object 3: extends Object 2 and shares a foreign key, may have multiple instances, one instance mandatory . There are 3 types of Object 3 which are dependent on a field from Object 2 (haven't yet figured out how these need to link without code).
• Object 4: extends Object 3 and shares a foreign key, may have multiple instances, not mandatory, so may be omitted.

Is there a way to dynamically generate Object 3 through MySQL? Like if field #3 shows Object 3.1 call that table, if it shows 3.2 call that instead. Similar to a constructor.

Also is there a way for me to map related objects? For example, since everything is dependent on Object 1, I'd like to be able to call Object 1 and have it pull all related instance of Objects 2, 3, and 4 related to that specific object. I was thinking of creating a separate table to hold primary keys of related objects, but I'm thinking there is probably a better way of doing that.

Also I've read that I shouldn't use stored procedures, and instead do everything with code. Is that correct, because I would think for my model it would be more convenient and cleaner to just call on Object one, and have MySQL do all the joins internally and return a single completed object

Thank you very much for your help.

Answer 1

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.

Answer 2

My question is this, is there a way to dynamically generate Object 3 through MySQL. Like if field #3 shows Object 3.1 call that table, if it shows 3.2 call that instead.

Certainly you can use views to generate content, but MySQL doesn't really handle views very well. I hate to recommend db changes, but one thing you do get from moving to PostgreSQL is a very mature framework for db-level programming and an ability to do most of this relatively sanely.

If you are on MySQL, I would look at doing your dynamic generation in your application. This is because MySQL makes a bunch of optimizations for single application db's and assumes close coupling there (otherwise allowing applications to set sql_mode wouldn't make much sense), so it is a fair assumption that the db will be used only with one application codebase.