Don't forget that in a relational SQL based database it is good practice
wherever possible to build a unique key from actual data in the row( record
) . This might very often require a JOINT key to be used, for example
surname and postcode. To use automatically generated numeric keys for every
table is often missing out on some of the benefits of an SQL based
relational databae ( in fact it has more in common with the older "network"
style databases like 4D) . For example by using a joint key composed of data
in your row, you are ensuring a level of data validation and integrity. For
example the database can ensure that there is no duplication of records with
the same surnamer and postcode. Sometimes, in order to avoid the
performance hit of joint keys, developers will create a single text column
holding a concatenation of two other columns such as surname+postcode. This
is done to use a single index as opposed to two.
Automaticaly generated integer keys should only really be used where there
no suitable ( user entered ) data in the table that can uniquely identify
the row.
Sorry if this is teaching grannies to suck eggs, but its worth repeating -
for those new to the world of IT or systems analysis.
Regards
Hi Dan,
Interesting. Unfortunately I don't agree with you ;-)
I realise it might just have been a quick example, but I sure
wouldn't use Surname and Postcode as my primary key; My brothers who
love in the same town would hate such a database table built on this
key...
From experience building in user logic into a key just leads to
problems down the track (either from things the db designer didn't
anticipate or users starting to use the key in inappropriate ways.
For us, in dozens of scientific apps, a non-identifier Unique integer
key is best.
--
Cheers,
Dr Gerard Hammond
MacSOS Solutions
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
