Posted by Rik on 10/19/06 03:42

linda wrote:
> "Breklin" <breklin@sbcglobal.net> wrote in message
> news:oGzZg.988$T_1.417@newssvr14.news.prodigy.com...
>> I wrote you a simple script quite a few posts ago that use both a
>> record id (auto_incremented) and a productid, which you get from
>> your supplier. It seems that Steve agrees with my approach, too, of
>> using both. One is
>> a unique id for the table record you are dealing with which can be
>> used for quick look up in your queries and WHERE statements. The
>> other is for you to validate against it's existence. I do this with
>> several ecom
>> sites I have built and it works great because, if, I am updating a
>> particular record, I will use the auto_incremented 'id' field to
>> update the record data. If I am inserting, I want to make sure that
>> there are
>> no duplicate records for the productid that was manually entered, i
>> can use that to make sure i'm not duplicating that product.
>>
>> See if you can find what I wrote. Otherwise, I'll pound it out again.
> I have as of tonight, (having given it a lot of thought) come round
> to the idea perhaps I should use a separate Id field, and have now
> added this. Though I must admit I don't fully understand how it would
> prevent any duplicates.

A particular ID does not prevent any duplicates, but gives a nice reference
to particular data. A combination of data (values in the rest of the
fields) should be given a reference, so one particular data-set is unique,
and easily referencable.

When adding a particular type to your table (a 'unique' combination af
values) it is wise to check wether that unique combination already exists
(check wether ALL the other fields are the same) and instead of adding a
new record return the specific ID of the record already in existance.

If all the fields are the same, but the type of data it represents is
different, you'll most likely have a problem in the configuration of your
database (not always, there are exceptions). In that case, rethink what
exactly is 'different' about that type, and try to capture that in said
database.

On inserting some data, you can then match it to ALL the other fields, and
check wether that particular record already exists. If not, create a new
one with the field values as given, if it exists, use the ID field as a
reference in the rest of your database.

When enabling the user to change a certain ID's values, check:
- all values are correct, and if they refer to another table, that the
relevance is indeed correct.
- all data relevant to that ID should change.
- if not any of the previous, create a new ID, with the rest of the fields
as they should be for the new data-type.

Autoincrementing the ID-field will mean that for every data-type in your
table, there will be a unique number, which will be automatically added by
the database itself when adding a ne record without setting that ID
specifically. (In case of mysql, an insert ID can be retrieved with
mysql_insert_id())
--
Grtz,

Rik Wasmus

[Back to original message]