Accessing Database without Primary (or Foreign) Key

[Diesel]

Ok, here's the problem...one that I've had for a little while now. Say I have a Customer Record on the database, I retrieve the record and place it into an Customer object. This object does not contain the customer's ID (primary key) because it is not needed within the program and Im seperating the class functions from the database functions and so on. Anyway, the object manipulates the data and such during runtime and now it's time to reinsert it back into the database...without the primary key. I can reference a record using all of the attributes except for the primary key, but that is not garaunteed to be unique.

 

Without putting the Customer's ID into the Customer object itself, how can I do this? Also, same thing goes for foreign keys. Say I have an Employee Object and in the database base there is a field for the manager's id, but an Employee object in and of itself does not keep track of the managerId, when I insert an Employee object into the database, where does the managerId come from.

 

Any suggestions are welcome...

[Joe Mamma]

Ok, here's the problem...one that I've had for a little while now. Say I have a Customer Record on the database, I retrieve the record and place it into an Customer object. This object does not contain the customer's ID (primary key) because it is not needed within the program and Im seperating the class functions from the database functions and so on. Anyway, the object manipulates the data and such during runtime and now it's time to reinsert it back into the database...without the primary key. I can reference a record using all of the attributes except for the primary key, but that is not garaunteed to be unique.

you need the ID

 

Without putting the Customer's ID into the Customer object itself, how can I do this? Also, same thing goes for foreign keys. Say I have an Employee Object and in the database base there is a field for the manager's id, but an Employee object in and of itself does not keep track of the managerId, when I insert an Employee object into the database, where does the managerId come from.

hmmm. . . arent managers employees?

 

Employee should have a mangerID fk self referential back to the employee table.

Employee object should have both its ID and its managers ID in it.

[Diesel]

Right, but again, Im not storing the employee id. There has to be a way to do this. Im basically just creating an object layer that access a database layer in order to manipulate the database fields. I need more brain power!

[kejpa]

Right' date=' but again, Im not storing the employee id. There has to be a way to do this. Im basically just creating an object layer that access a database layer in order to manipulate the database fields. I need more brain power![/quote']

The easiest and most safe way to do this is using the EmployeeID, most (all?) other properties can change and there might be duplicates. The only thing you know for sure is that the EmployeeID won't be duplicated and it won't change.

So, you read that field from the database aswell, you pass it on to your GUI or front-end (but don't show it if you don't like) After manipulating the record you send the new data back along with the ID and update the database.

So simple so smooth, why not give it a try?

[Nerseus]

There has to be a way to do it

Nope - if you have a Primary Key and your intention is to eventually update the database then you must store the primary key somewhere.

 

Just because your object contains the database's primary key doesn't mean it has to be exposed. If the object has a method to save itself back to the DB, that method could use the private primary key to do it.

 

For child objects you have two options: they can contain the fk value (the CustomerNumber for example) or get it from the parent object. If you wanted to make it more generic, maybe add a new interface called IPrimaryKey that adds a method GetPrimaryKey() that returns the int value. I don't see much benefit in that unless you need that level of abstraction.

 

I would guess you have code somewhere that relies on the relationship between the parent and child and so a known reference between the two is fine.

 

-ner

[fizzled]

This object does not contain the customer's ID (primary key) because it is not needed within the program ... now it's time to reinsert it back into the database...

 

Seems to me you've pretty much contradicted yourself already. You say you don't need it, but then you need it.

[Diesel]

No, the class does not use the ID, but the database does. Anywho...I guess I do have to store the primary key. Oh well, not a big deal. Thanks everyone.

[samsmithnz]

I agree with the others, you have to bring the employeeID along with the rest of the data. You say you don't need it to display the data and that is true, - but you still need it to update. Its not a big deal, just bring it with the employee object and send it back when you're done, we all do it.

[Nerseus]

Keep in mind a lot of classes have private fields to keep track of things that won't ever be exposed through a public property or method, but they're still necessary.

 

-ner