On Oct 16, 2006, at 11:51 AM, Daniel Stenning wrote:
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.
Many times normalizaton practices would dictate that you have a
possibly meaningless unique key.
Sometimes there is data that is unique per person that makes a nice
key (social insurance number, employee number, etc)
But sometimes there is no such candidate key and you have to invent one
In many databases rowid is a nice identifier for this purpose as it
has no implicit meaning
And, that's the context in which this whole thread started. SQLIte
has a rowid, but, buy default it is NOT a great candidate because
SQLite does not insure that row id's are unique primary autoincrement
columns. However, as Marco pointed out IF you define a key with those
attributes then you will have one in you schema.
That's all I was suggesting; that you explicitly define such a key
and not assume that one will be provided or created for you by the
database engine.
_______________________________________________
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>
