php mysql database-design laravel laravel-4

DB Structure : Multiple tables with common ID

I'm building an app that need to manage various objects (3 at the moment, but that might increase in time). All these objects have a unique ID using the same format, but no other attribute in common.

So I created a table for every object, but I'm wondering how to do an optimized search by ID.I want to build a good process form the start, because the total number of rows could become very high, and I don't want to have to rewrite code in a couple of months because it would have become too slow.

I thought of NoSQL databases, but I am required to use MySQL. The PHP code uses Laravel 4 with the Eloquent ORM.

Let's say I want the item with ID abcd-123456, I have no idea which table to query, so I thought of this :

When inserting an object, store the ID along with the table name in another table (CommonIndex)
When querying by ID, lookup the table name in the CommonIndex table, store in the $tableName variable
Retrieve the final data by using $tableName::find('abcd-123456') in Eloquent (using models nammed exactly like my tables)

But I'm worried this process will become sluggish when I have to search my ID in 300k+ rows

Any thoughts about how to improve this process, or building a new one ?

Thanks !

EDIT : More informations:

My tables are not linked to each other, each one represent a type of object
Each table has an ID field
Each object has a unique ID, but on the same format (ex : Table 1 contains objects ab-1 de-3 hi-5 Table 2 cd-2 gh-4 jk-6 etc...)
Two objects from different types cannot have the same ID
I do not assign the ID, each object already has one
Most of the searches will be done by ID, because it's easier for the users
In specific conditions, users may search a product on a different field than the ID, but I don't have the same worries about performance, because it will be very rare
To allow for searches on others fields, these specific fields will be indexed (1 or 2 per table)
The search will be one by one
If I add a batch search along the way, I will process it one by one

Solution

I don't have a definitive answer, I think it depends on your target system (the server where your code will be) and the number of requests you'll have. But if the number of request you get at the same time is low and if you have a fairly good server, I would go with what you suggested. Just be sure to add indexes in the tables. It's fairly easy to add dummy data and test it with 300k rows.

One possible solution :

One simple thing you could do (depending on your needs), is to use just one table with 2 columns :

Your ID
The PHP object stored as a string (aka serialized)

There's downsides though. Check out this URL for pros and cons : http://www.mysqlperformanceblog.com/2010/01/21/when-should-you-store-serialized-objects-in-the-database/

(for example, if you need to search on other property than your id, it won't work. If you need to update the DB often, it's not what's more efficient either)

It's really easy to serialize and unserialize object in PHP :

$a = your_object;
$s = serialize($a);
// save data into database. $s is now your object, but in a string format.

// retreive the value from your database ($s)
$s = get_from_database($id);
$a = unserialize($s);
// do whatever you want now with your object

Another solution is the one you mentioned but I wouldn't store the table name. A number is more efficient.

Since you can't really store the serialized object, I think what you suggested is the best way. 300k for MySQL is manageable, just ensure that you have an index on your id column.

Also, if there are often searches for a particular group of columns (for example the users often search by id, first name and last name), you'll want to use a composite index on both columns (it takes more disk space tough).

If you want to be certain that the queries (1 to get the table and the 2nd to get the data) will be efficient, you can easily enter 300K entries with a small php script (a loop with inserts) or with data generators (I found this one : http://www.generatedata.com/).

I would enter 300k in 2 tables (in your "index" table and one of the object table) and test the time it takes to make 2 queries, one on the "index" table and the other one on the object table.

Another thing you could try is using a stored procedure (you could do the choice of the table based on the type of the object in the stored procedure).