Well although I still agree with you, the example given isnt one that I would use either, but its all I could come up with at the time :)
The principle of linking relational tables via real data is part of the whole RDB approach as tought by those old masters of Systems Analysis, Mr Codd and Date. The topic is naturally a huge one, each case needing to be looked at seperately. However it is all very standard data modelling fare and used every day pretty much everywhere. Every business analyst I ever worked with in the "Square Mile" ( London Finance district ) used exactly the same methodology. I recommend reading up on Codd and Date, or maybe the new UML modelling methodology developed by Rational. Not knowing your data and how you chose to model it I cannot say whether or not I would do the same as you in your situation, but the principles I mention are very much established and proven and it definitely IS bad practice ( and often a wrong data model ) to AS A RULE always simply let each table have a uniquely generated automatic key. Sure there are many cases where that is the right thing to to, but lots of others where it really isnt. It is perfectly possibly to build a database where no joint keys are used, but it can often lead to databases where the data quickly becomes "unclean". On 16/10/06 23:35, "Dr Gerard Hammond" <[EMAIL PROTECTED]> 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. >> >> 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. _______________________________________________ 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>
