 | |
#41
 
| |||
| |||
|
Re: Use IDs or strings? -
12-03-2008
, 11:39 PM
|
"paul c" <toledobythe... (AT) oohay (DOT) ac> wrote in message news:qTxZk.2301$si6.1497 (AT) edtnps83 (DOT) .. Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? * * If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. *The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. All right, the tribe has spoken. *The word "users" is to be reserved for people. *So be it. However the question of whether Applications code must have access to internal IDs is not as automatic as the above requests indicate. Particularly is more than one application uses data from a single database. If the ONLY purpose behind ID's is to join *foreign keys with the keys they reference, then a view performs the join need not include the ID in the view columns.. If an application only uses the the view, and doesn't have access to the underlying tables, then the ID column, and the foreign key are effectively hidden from it. Even in a situation where the ID is revealed to the appliction, *the application will generally have to maintain the relationship between user visible keys and ID fields. |
Quote:
|
Example: *in a job seeker database, the JobSeekerID was generally hidden from the users *(except for certain administrative users). *The usersused SSN to identify Job seekers *(with dummy SSNs for job seekers with no SSN or with an unknown SSN). *A certain job seeker was enetered in the system with the wrong SSN *(a typo). *Later, a correct entry was made with the right SSN, and a different ID, of course. *Straightening out this mess required manual intervention. |
but SSN IS NOT a surrogate ID. It is not the kind of ID I was
discussing. You really gave a poor example.
Internal ID's are exactly that: INTERNAL to the DATABASE. Even
"Administrative users" should not see these IDs. code and developers
should be the range of their exposure.
The fact that all too many systems expose these IDs is not
justification for making this error in new systems.
Now there are cases where an ID does become useful. If that is the
case, then the ID should be designed with certain data integrity built
in, e.g., a check digit. It is certainly easier to use a VIN or SSN to
identify a vehicle or a person. The VIN is a good example because it
is basically a summary of key attributes of a vehicle, encoded in a
standard way. Otherwise the PK of a vehicle table becomes a composite
key of Manufacturer, Model, year, etc.
(Hey I may be emphatic, but I am not dogmatic.)
If it is planned to be exposed to users, then do it right. IOW, the ID
in that case should NOT be just s sequence number.
Ed
#42
| |||
| |||
|
|
"paul c" <toledobythe... (AT) oohay (DOT) ac> wrote in message news:qTxZk.2301$si6.1497 (AT) edtnps83 (DOT) .. Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? * * If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. *The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. All right, the tribe has spoken. *The word "users" is to be reserved for people. *So be it. However the question of whether Applications code must have access to internal IDs is not as automatic as the above requests indicate. Particularly is more than one application uses data from a single database. If the ONLY purpose behind ID's is to join *foreign keys with the keys they reference, then a view performs the join need not include the ID in the view columns.. If an application only uses the the view, and doesn't have access to the underlying tables, then the ID column, and the foreign key are effectively hidden from it. Even in a situation where the ID is revealed to the appliction, *the application will generally have to maintain the relationship between user visible keys and ID fields. |
Quote:
|
Example: *in a job seeker database, the JobSeekerID was generally hidden from the users *(except for certain administrative users). *The usersused SSN to identify Job seekers *(with dummy SSNs for job seekers with no SSN or with an unknown SSN). *A certain job seeker was enetered in the system with the wrong SSN *(a typo). *Later, a correct entry was made with the right SSN, and a different ID, of course. *Straightening out this mess required manual intervention. |
but SSN IS NOT a surrogate ID. It is not the kind of ID I was
discussing. You really gave a poor example.
Internal ID's are exactly that: INTERNAL to the DATABASE. Even
"Administrative users" should not see these IDs. code and developers
should be the range of their exposure.
The fact that all too many systems expose these IDs is not
justification for making this error in new systems.
Now there are cases where an ID does become useful. If that is the
case, then the ID should be designed with certain data integrity built
in, e.g., a check digit. It is certainly easier to use a VIN or SSN to
identify a vehicle or a person. The VIN is a good example because it
is basically a summary of key attributes of a vehicle, encoded in a
standard way. Otherwise the PK of a vehicle table becomes a composite
key of Manufacturer, Model, year, etc.
(Hey I may be emphatic, but I am not dogmatic.)
If it is planned to be exposed to users, then do it right. IOW, the ID
in that case should NOT be just s sequence number.
Ed
#43
| |||
| |||
|
|
"paul c" <toledobythe... (AT) oohay (DOT) ac> wrote in message news:qTxZk.2301$si6.1497 (AT) edtnps83 (DOT) .. Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? * * If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. *The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. All right, the tribe has spoken. *The word "users" is to be reserved for people. *So be it. However the question of whether Applications code must have access to internal IDs is not as automatic as the above requests indicate. Particularly is more than one application uses data from a single database. If the ONLY purpose behind ID's is to join *foreign keys with the keys they reference, then a view performs the join need not include the ID in the view columns.. If an application only uses the the view, and doesn't have access to the underlying tables, then the ID column, and the foreign key are effectively hidden from it. Even in a situation where the ID is revealed to the appliction, *the application will generally have to maintain the relationship between user visible keys and ID fields. |
Quote:
|
Example: *in a job seeker database, the JobSeekerID was generally hidden from the users *(except for certain administrative users). *The usersused SSN to identify Job seekers *(with dummy SSNs for job seekers with no SSN or with an unknown SSN). *A certain job seeker was enetered in the system with the wrong SSN *(a typo). *Later, a correct entry was made with the right SSN, and a different ID, of course. *Straightening out this mess required manual intervention. |
but SSN IS NOT a surrogate ID. It is not the kind of ID I was
discussing. You really gave a poor example.
Internal ID's are exactly that: INTERNAL to the DATABASE. Even
"Administrative users" should not see these IDs. code and developers
should be the range of their exposure.
The fact that all too many systems expose these IDs is not
justification for making this error in new systems.
Now there are cases where an ID does become useful. If that is the
case, then the ID should be designed with certain data integrity built
in, e.g., a check digit. It is certainly easier to use a VIN or SSN to
identify a vehicle or a person. The VIN is a good example because it
is basically a summary of key attributes of a vehicle, encoded in a
standard way. Otherwise the PK of a vehicle table becomes a composite
key of Manufacturer, Model, year, etc.
(Hey I may be emphatic, but I am not dogmatic.)
If it is planned to be exposed to users, then do it right. IOW, the ID
in that case should NOT be just s sequence number.
Ed
#44
| |||
| |||
|
|
Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? * * If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. *The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. |
If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed
#45
| |||
| |||
|
|
Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? * * If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. *The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. |
If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed
#46
| |||
| |||
|
|
Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? * * If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. *The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. |
If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed
#47
| |||
| |||
|
|
Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. |
If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed
Elsewhere, it's been said that a key should NOT carry any information beyond
identifying something. You seem to be saying the opposite. Am I reading
you right?
#48
| |||
| |||
|
|
Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. |
If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed
Elsewhere, it's been said that a key should NOT carry any information beyond
identifying something. You seem to be saying the opposite. Am I reading
you right?
#49
| |||
| |||
|
|
Ed Prochak wrote: On Dec 1, 10:07 am, paul c <toledobythe... (AT) oohay (DOT) ac> wrote: Ed Prochak wrote: ... Are these IDs known to users? If the answer to either of these is yes, then use the IDs. ... If they are useful (pertinent if you like), they will become known. The only decision to make about such ID's is whether they have a pertinent use in one of the applications. Applications are not the users I was referring to. Applications (code) are part of the system and must therefore have access to such ID's. But that's not relevant to the original question. ed Alright, to be pedantic, if they are useful to users they will become known by users. |
If so, it is better to plan and design such ID's rather than just
exposing a sequence number value. A VIN (Vehicle Identification
Number) is a good example. It isn't perfect, but it goes a long way
beyond just a number.
Ed
Elsewhere, it's been said that a key should NOT carry any information beyond
identifying something. You seem to be saying the opposite. Am I reading
you right?
#50
| |||
| |||
|
|
If so, it is better to plan and design such ID's rather than just exposing a sequence number value. A VIN (Vehicle Identification Number) is a good example. It isn't perfect, but it goes a long way beyond just a number. Ed Elsewhere, it's been said that a key should NOT carry any information beyond identifying something. You seem to be saying the opposite. Am I reading you right? |
Mr. Elsewhere is wrong.
The following links just scratch the issue. I suppose it's been going on
for more than 20 years.
http://it.toolbox.com/blogs/database...il-part-i-7327
http://it.toolbox.com/blogs/database...l-part-ii-7345
http://it.toolbox.com/blogs/database...-part-iii-7365
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.