Why do they insist on repeating the name of the table in the column name?
-
I must say that I hate that too. And it is even worse when the fields get too long and so they start to abbreviate parts of the name (be it of the table name or the column name)... it makes it impossible to create C# code that automatically generates queries without using alternative methods to say: Hey... Id becomes "SOME_ABBV_TB_ID".
I can think of something worse ... I'm having to deal with a database (designed by someone else I hasten to add) where table names and column names have spaces in them e.g. [Current Sterling Rate]. :( Drives me nuts. But on a positive side - at least I don't have to hit the Shift key again to get the []
-
Cheers Eddy It's the trade-off between the long compound key and the simplicity of the id (but then, as you have said, it can be uniquely indexed against the id without being the primary key). A proper compound key does avoid the problem of duplicates amongst things that should be unique (I think this will get more weight in my mind in future) Which leaves me with only look-ups that are basically a description, but you don't want to have the description as the foreign-key, or you are saving no storage space, so you put an Id column on there to use as foreign key. Now, the above argument about uniqueness for the description can be used. I think you have convinced me - just need to stop thinking 'We need a thing, better have a thing table; first field thing.id, a primary key! (not thing.thingid, but this is where we started ;) ) Regards, Stewart
Stewart Judson wrote:
Which leaves me with only look-ups that are basically a description
There's no sweet way of preventing duplicates there; the only alternative that has been given to me, was to replace them with a varchar-field in the table that originally referenced them. Sounded weird at first, until I saw the implementation - it did a
SELECT DISTINCTon that column, showing the results in a drop-down. That's not always a usefull alternative, but it's nice to have options.Stewart Judson wrote:
I think you have convinced me - just need to stop thinking 'We need a thing, better have a thing table; first field thing.id, a primary key! (not thing.thingid, but this is where we started ;) )
Start thinking "how will the user differentiate between his real-life thingies, and can we use those properties to uniquely identify a thingy-record on screen" :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Jörgen Andersson wrote:
an entity should have the same name everywhere is a good rule to follow, but not at any cost
Correct. The name should give some context not just datatype information; e.g. why is the particular User associated with the current record? What part does he play in this little drama?
I just remembered, if your database is ISO SQL-92 compliant there is yet another point in using the same name of your ID fields. Check this out:
SELECT *
FROM CUSTOMERS
JOIN ORDERS
USING CustomerIDThis doesn't work on SQL Server or Sybase.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
I just remembered, if your database is ISO SQL-92 compliant there is yet another point in using the same name of your ID fields. Check this out:
SELECT *
FROM CUSTOMERS
JOIN ORDERS
USING CustomerIDThis doesn't work on SQL Server or Sybase.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
As well it shouldn't.