Application Design Issues

[billy_bill_1]

Hi all,

I'm having problems designing an app. My problem lies with proper object representation of data and interfacing with a database.

The system is under development and using non-oo techniques is not possible.

Many features of the program require mapping a record in one table a, to records in other tables b, c etc.

IE, in the same script:
1. Insert record into table a.
2. Link record in table a to record in table b, c etc.

The problem I'm facing is how to use objects to represent records in these tables without redudency. Redundency meaning in this case variables not represented inside objects by their actual value and instead be created on the fly when the object is inserted into the database table.

Example: Object A has $ID as a field. Instead of being able to initialize it on object creation with some value x, having to have $ID = "" and then the id being created and initialised by the auto_increment property on insertion into the database.

Why is this a problem in my case? Because i need to minimise query numbers to the database as much as possible while still providing the easy interface that objects offer.

A->insertTableA() //insert object A's data into table a

A->insertTableB() //insert object A's other data into table b, (record in table a and b will be linked by a unique id)

A->getId() //get the ID field of the object.

Problem: If A is created in same script as insert() AND getId() will be used a problem is ecountered. How do I get $ID field using getID() as $ID = "" and as such obviously does not match the value in the auto_increment column in table A. I therefore cannot link the record in Table A to the record in Table B without using another query to find out what the ID in table A is so i can the insery it into table B along with other data and therefore create a link.

Representing an object in this way = crap design. Performing as many querys as this = crap performance/resource usage.

I'm currently performing the query to get the ID from inside the object. Hiding this process from the programmer. This way in my opinion is the sellotape and bubblegum method.

I have decided that making sure each object has a field that must be unique and then using hash(uniquefield) to grab a unique alphanumeric id for that object. However, using an alphanumeric key as an Id sheds the ability to gain performance from numeric id representation (when performing select from.... where ID = ... querys)

Having a unique field for each object really removes the neccessity for having an ID, however, if all the fields are very long, the hash(field) will be long and slow to compare in querys and so would the plain old compare with field.

Can anyone offer me suggestions of how to get around this problem. Like maybe getting a unique numeric id from a unique alphanumeric field.

Thanks for reading this incredibly long question (it got away from me a bit ;-) )

Billy

[kparker]

What database are you using? Every dbms that provides auto-generated id numbers also provides a means to retrieve them, but (alas) it's different for every dbms. One thing that is common, as it's inherent to the webserver situation, is that you must call the function right after you issue the INSERT, and before you do anything else, so you can get the right ID associated with the right row.

The best way to do this is to hide this code inside insertTableA(), that way the programmer can't forget to do it.

And since you mentioned design issues (he said, sticking his neck out) it looks like the object design isn't really doing what it should here. If the data always needs to be inserted into these tables in this particular way, the last thing you want to do is expose that to the world. It makes the object look like that wonderfully memorable phrase of Stroustrup's: "a bag of bits and bytes with get and set functions." What you probably really need here is something like A->commit_to_storage(), which does all the busywork housekeeping, completely out of site of the application programmer.

[billy_bill_1]

Hi Kirk,

I'm using the ADODB database abstraction layer. I'm using MySQL to develop the app, mainly because i have a lot previous experience with it. When I finish development it won't take much query tweaking to get an Oracle, SQL Server and progreSQL version going.

I dont know about Oracle or SQL Server but mysql's Last_Insert_Id() function only returns the last auto_increment value from the individual connection that is in use. It is only valid on a per connection basis so if another user happens to change something at the same time then all hell breaks lose. There is the same problem with doing something like SELECT id from tablea where otherfield = fieldname
then adding 1 to the result. Both methods i have tried. Both methods are inadequate.

I am hiding all these methods from the public so to speak so thats not a worry. What im worried about is the analogy of "building a rock solid house on sand" where the sand in this case is unnecessarily complex functions and design. And as we all know that as the complexity of a system grows so too does maintainance, system resource needs and the likelyhood of serious bugs and flaws.

What I'm looking for is the most efficient and simple way of creating a Unique Id. And I'm absolutely positive there is a better one that what I have thought up.

Cheers,
Billy

[billy_bill_1]

I was just thinking. Maybe a table of generated id numbers n to x.

CREATE TABLE IDS (ID BIGINT not null , taken ENUM ("true", "false") DEFAULT 'false' not null )

Populate that table with from say 0-1000000 and use it to grab an id. Every time an id is taken simply mark "taken" field with true.

Not that good when it comes to storage but as there is some duplication but database server load is my main problem and this method should reduce it by one query per use should it not?
Help!

Thanks,
Billy

[kparker]

You appear to misunderstand what last_insert_id() does. When you do an INSERT that generates an autoincrement being applied to the new row, the mysql back end that your connection is talking to retains a copy of that generated number. As long as you ask for it before you (a) terminate the session (including implicitly, which happens at the end of every php script) or (b) issue another INSERT, it will reliably give you the exact autoincrement value that you generated in that just-completed INSERT. This is completely independent of anything that any other client is doing. Of course, it's not impossible that there could be a bug in this function, but I've certainly never ever heard of one.

[kparker]

The real answer, which will create a far smaller load on the database server, is to figure out why mysql_insert_id() isn't working correctly.