Identifier for an object when storing in a relational database

[nacho222@gmail.com]

I'm currently in the middle of writing a persistence framework, and I
have to make a design decission. The framework will take care of all
the saving and restoring objects, and also the relationships between
them. Obviously, I need a way to identify objects uniquely. Now, one
way would be to use an autogenerated id from a autoincremental field in
the DB. But the problem is that I need to know the ID of the object as
soon as it is created, to be able to relate it to other objects. So, I
have come up with three possible solutions, none of which satisfies me.

The solutions are (in my current order of preference):
* A string ID, generated using uniqid. Main drawbacks: uniqid is a slow
function, and having a string as a primary key raises an alarm in my
head, as I tend to think (without any knowledge to back it up) that it
would be somewhat innefficient.
* An ID generated by an "ID manager", that serves IDs for each object
creation. Main drawbacks: it would have to read the "next id" from the
database for each request, and then update, as the manager can't be
shared between concurrent requests. Two database queries per object
creation. Add to that the object saving when the script ends.
* The autonumeric ID from the DB, only saving the object as soon as it
is created. Main drawbacks: extra save for the object (it will be
modified and saved later in almost every case), the object maybe ends
up being temporary (that shouldn't happen, you don't need a persistent
object for that, but who knows)

I would like to get comments on these methods, which of them you think
is best, and also if you can come up with any other (hopefully better)
methods.

Thanks in advance.

[Erwin Moller]

Re: Identifier for an object when storing in a relational database

nacho222@gmail. com wrote:
[color=blue]
> I'm currently in the middle of writing a persistence framework, and I
> have to make a design decission. The framework will take care of all
> the saving and restoring objects, and also the relationships between
> them. Obviously, I need a way to identify objects uniquely. Now, one
> way would be to use an autogenerated id from a autoincremental field in
> the DB. But the problem is that I need to know the ID of the object as
> soon as it is created, to be able to relate it to other objects. So, I
> have come up with three possible solutions, none of which satisfies me.
>
> The solutions are (in my current order of preference):
> * A string ID, generated using uniqid. Main drawbacks: uniqid is a slow
> function, and having a string as a primary key raises an alarm in my
> head, as I tend to think (without any knowledge to back it up) that it
> would be somewhat innefficient.
> * An ID generated by an "ID manager", that serves IDs for each object
> creation. Main drawbacks: it would have to read the "next id" from the
> database for each request, and then update, as the manager can't be
> shared between concurrent requests. Two database queries per object
> creation. Add to that the object saving when the script ends.
> * The autonumeric ID from the DB, only saving the object as soon as it
> is created. Main drawbacks: extra save for the object (it will be
> modified and saved later in almost every case), the object maybe ends
> up being temporary (that shouldn't happen, you don't need a persistent
> object for that, but who knows)
>
> I would like to get comments on these methods, which of them you think
> is best, and also if you can come up with any other (hopefully better)
> methods.
>
> Thanks in advance.[/color]

Hi,

IMHO your third solution is very good, mainly because you make sure that
every object has a DB-related unique identifier, which you relate to the
(serialized?) object you store in it.
Your second solution (the ID-manager) could actually rely on this if you
choose to implement it that way.

[Postgresql]
One sidenote: If you use Postgresql you could decide to use the
serial-function to retrieve a new ID WITHOUT actually storing it right
away.
(Maybe other DB's have this option too, I am not sure, but ever since I
learned PostgreSQL my interest for other DB's faided away. ;-)

Something like this:
supose you have a table like this:

CREATE TABLE tblobjects(
objectid SERIAL PRIMARY KEY,
serobject text
)

Now you can get the next serial/autonumber like this:
$SQL = "SELECT nextval('public .tblobject_obje ctid_seq'::text ) as
mynextobjectid; ";
// execute it

The serialname will differ of course, so look it up.

In that way you get the next ID with minimal CPU-cycles.
Use the required ID later to insert.
In Postgresql you CAN insert your id in a serialfield (as long as it doesn't
exists of course).
Also note that everytime you call nextval(someser ial) the number will be
increased by 1, so you are safe to use it later like this:

$SQL = "INSERT INTO tblobjects (objectid,serob ject) VALUES
($yournewidhere ,$yourserobject here)";

Just my 2 cent.

Good luck.
Your project sounds like fun. :-)

Regards,
Erwin Moller

[Chung Leong]

Re: Identifier for an object when storing in a relational database

nacho222@gmail. com wrote:[color=blue]
> I'm currently in the middle of writing a persistence framework, and I
> have to make a design decission. The framework will take care of all
> the saving and restoring objects, and also the relationships between
> them. Obviously, I need a way to identify objects uniquely. Now, one
> way would be to use an autogenerated id from a autoincremental field in
> the DB. But the problem is that I need to know the ID of the object as
> soon as it is created, to be able to relate it to other objects. So, I
> have come up with three possible solutions, none of which satisfies me.
>
> The solutions are (in my current order of preference):
> * A string ID, generated using uniqid. Main drawbacks: uniqid is a slow
> function, and having a string as a primary key raises an alarm in my
> head, as I tend to think (without any knowledge to back it up) that it
> would be somewhat innefficient.
> * An ID generated by an "ID manager", that serves IDs for each object
> creation. Main drawbacks: it would have to read the "next id" from the
> database for each request, and then update, as the manager can't be
> shared between concurrent requests. Two database queries per object
> creation. Add to that the object saving when the script ends.
> * The autonumeric ID from the DB, only saving the object as soon as it
> is created. Main drawbacks: extra save for the object (it will be
> modified and saved later in almost every case), the object maybe ends
> up being temporary (that shouldn't happen, you don't need a persistent
> object for that, but who knows)
>
> I would like to get comments on these methods, which of them you think
> is best, and also if you can come up with any other (hopefully better)
> methods.
>
> Thanks in advance.[/color]

I would try a combination of 1 and 3. A unique string id has the
advantage of being unique universally, which makes your object less
tied to the database. I have ran into situations where data has to be
exported/imported between different instances of the application, where
a auto-increment key was inadequate.

Here's a possible scheme:

* Call uniqid() once in the script, then generate subsequent ids by
appending an incrementing number.

* An id of an object would have two parts: the unique string and the
database key. When created, the object acquires only the unique string.


* Obtain the database key only if the object is saved. Save the
database key to a hash table so that objects holding ids with just the
unique string can obtain it.

* When saving an object that links to another, check the database key
part of the id. If it's null, then save the linked-to object first and
obtain the datakey key from the aforementioned hash table.