I had great respect for Caddre's KB until you admitted to passing the SQL Exam ;) Perhaps a touch of humility might help defuse the situation, I'm always a little suspicious of anyone who claims to know it all. I think the original, "Does anyone see a reason
why an identity column would be more or less desireable" is getting lost in the world of forum egos. I think it's obvious Caddre has a lot of Db experience and Xanderno has some interesting points to make, let's just call it a day before I'm strangled by my
halo.
Thanks much appreciated and I passed the exam through humility, by begging all ANSI people accessible by email because it had a high fail rate and I now sorry I said something about it.. Thanks again.
What about GUIDs. That is the most widely used identifier and it would totally fail your criteria. What if you had to import a taken over company's emplyee list and they also had an existing identity column starting at 1?
VIN and SSN are similar systems that almost ensure uniqueness and are therefore so ubiquitious.
I will concur with the rest of the guys in this thread that you( mru22 ) could benefit from some basics. You don't have to heed us. The world is full of people trying to reinvent the wheel
and hoping it would be rounder.
"Person" is a very poor naming choice for an individual...
Actually, for the sake of normalization both Employees and Customers ought to relate to a Persons table with the most basic of fields like fName,lName etc., SSN, gender.
"Person" is a very poor naming choice for an individual...
Actually, for the sake of normalization both Employees and Customers ought to relate to a Persons table with the most basic of fields like fName,lName etc., SSN, gender.
My $0.02
Rob
Better yet, why not just completely throw out data integrity and create a Things table and "subtype" everything? With your recommendation, only Customers can receive Paychecks, Employees can place orders, etc. Just immagine how much more fun it would be if
Products could place Orders!
Things is not a logical entity and would violate integrity. However persons is very much so and corresponds to real world too.
This kind of architecture could easily provide security policies like no employee allowed to be a customer too. In your architecture a substantial amount of code might need to be written to data-mine first.
Many models have a similar atomic building block - User in OS and AD. Check out the sample personalization Tables for v2.0.
Just immagine how much more fun it would be if Products could place Orders!
No. Employees can place orders. Why should the permission to allow an employee to place orders be dependent on whether his name is John or he is a she? Would you prohibit Pat from this duty if her SSN had a 7 as its third digit?
The only criteria you need is if the unique id is in the Employees table.
All the rest is data that belongs in a Persons table.
There is another axiom. "Moderatium omnibus" (Moderation in everything) The best approach is a balanced one that doesn't encroach on data integrity and inevitably, hopefully not too hard, on normalization.
This kind of architecture could easily provide security policies like no employee allowed to be a customer too.
Enforcing data integrity in the application? You do realize that's one of the reasons RDBMS were invented -- application-enforced DI does not work.
rmprimo
Alex Papadimoulis
Just immagine how much more fun it would be if Products could place Orders!
No. Employees can place orders. Why should the permission to allow an employee to place orders be dependent on whether his name is John or he is a she? Would you prohibit Pat from this duty if her SSN had a 7 as its third digit?
The idea is to track entities, not people. One who places an order is a customer, one who fills that order is an employee. It'd be like making a PiecesOfPaper table and subtyping it with "Bills" and "Paychecks". To the business, a customer and employee are
separate entities.
If I am a vendor contact then later switch companies and try to sell services to you, I become a new vendor contact.
rmprimo
The only criteria you need is if the unique id is in the Employees table.
So now, instead of using given keys, such as SSN or Employe_Num from HR, we have to further throw out DI by not making these primary keys? Just to prevent typing in the same first and last name?
Caddre
All-Star
26581 Points
5308 Posts
Re: Question on design with Identity columns
Aug 31, 2005 05:16 PM|LINK
Thanks much appreciated and I passed the exam through humility, by begging all ANSI people accessible by email because it had a high fail rate and I now sorry I said something about it.. Thanks again.
Gift Peddie
rmprimo
Contributor
3639 Points
738 Posts
Re: Question on design with Identity columns
Sep 20, 2005 03:22 AM|LINK
VIN and SSN are similar systems that almost ensure uniqueness and are therefore so ubiquitious.
I will concur with the rest of the guys in this thread that you( mru22 ) could benefit from some basics. You don't have to heed us. The world is full of people trying to reinvent the wheel and hoping it would be rounder.
Rob
Rob
rmprimo
Contributor
3639 Points
738 Posts
Re: Question on design with Identity columns
Sep 20, 2005 03:28 AM|LINK
Actually, for the sake of normalization both Employees and Customers ought to relate to a Persons table with the most basic of fields like fName,lName etc., SSN, gender.
My $0.02
Rob
Rob
Alex Papadim...
Member
480 Points
96 Posts
Re: Question on design with Identity columns
Sep 20, 2005 01:29 PM|LINK
Better yet, why not just completely throw out data integrity and create a Things table and "subtype" everything? With your recommendation, only Customers can receive Paychecks, Employees can place orders, etc. Just immagine how much more fun it would be if Products could place Orders!
rmprimo
Contributor
3639 Points
738 Posts
Re: Question on design with Identity columns
Sep 20, 2005 01:59 PM|LINK
Things is not a logical entity and would violate integrity. However persons is very much so and corresponds to real world too.
This kind of architecture could easily provide security policies like no employee allowed to be a customer too. In your architecture a substantial amount of code might need to be written to data-mine first.
Many models have a similar atomic building block - User in OS and AD. Check out the sample personalization Tables for v2.0.
Rob
Rob
rmprimo
Contributor
3639 Points
738 Posts
Re: Question on design with Identity columns
Sep 20, 2005 02:11 PM|LINK
No. Employees can place orders. Why should the permission to allow an employee to place orders be dependent on whether his name is John or he is a she? Would you prohibit Pat from this duty if her SSN had a 7 as its third digit?
The only criteria you need is if the unique id is in the Employees table.
All the rest is data that belongs in a Persons table.
There is another axiom. "Moderatium omnibus" (Moderation in everything) The best approach is a balanced one that doesn't encroach on data integrity and inevitably, hopefully not too hard, on normalization.
The tab is up to a dime now.
Rob
Rob
Alex Papadim...
Member
480 Points
96 Posts
Re: Question on design with Identity columns
Sep 21, 2005 01:23 PM|LINK
Enforcing data integrity in the application? You do realize that's one of the reasons RDBMS were invented -- application-enforced DI does not work.
The idea is to track entities, not people. One who places an order is a customer, one who fills that order is an employee. It'd be like making a PiecesOfPaper table and subtyping it with "Bills" and "Paychecks". To the business, a customer and employee are separate entities.
If I am a vendor contact then later switch companies and try to sell services to you, I become a new vendor contact.
So now, instead of using given keys, such as SSN or Employe_Num from HR, we have to further throw out DI by not making these primary keys? Just to prevent typing in the same first and last name?