Primary Key Selection

[aniruthan]

Hello everybody,

I am the newbie in the database normalization. What is the advantage of
using a surrogate key? I have designed a database where the user will
belong to one primary group and member of other groups. I designed the
entity(table) group with a surrogate key so that can referenced in a
users table for group membership details. One of my colleague insists on
using the group ( Ex. animation) itself as PRIMARY KEY instead of using
surrogate key since it reduces the number of queries ( Ex. To find out
the membership if I use surrogate key I have to refer to the groups
table and display the groups whereas if I use the group name itself as
primary key I need not use two tables)

The scanario is

groups table
group_id(PK) group name other fields
1 leather
2 IT enabled services
3 Education

user table

user_id primary_group secondary_group
1 1 2-3

In the second approach it becomes

group table
group_name(PK) other fields
Leather
IT enabled services
Education

users table
user_id primary_group secondary_group
1 leather Education-IT
enabled serrvices

Can anybody help me to resolve this?

Thanks in advance

 

[Tim Ferguson]

I have designed a database where the user will
belong to one primary group and member of other groups.

Hmmm: sounds like a many-to-many relationship to me, but read on...

entity(table) group with a surrogate key so that can referenced in a
users table for group membership details. One of my colleague insists on
using the group ( Ex. animation) itself as PRIMARY KEY instead of using
surrogate key since it reduces the number of queries

[ snipped lines indicate possible designs:

Users(*UserID, PrimaryGroup+, SecondaryGroup+)
Groups(*GroupID, FullName, etc)

or:

Users(*UserID, PrimaryGroup+, SecondaryGroup+)
Groups(*FullName, etc)

]

Well: it's arguable both ways. Plan (a) lends itself to a more flexible
arrnagement for the Groups: it's easier to sort if you choose a key that
allows it; and it's easier to make sure that the Keys never clash. On the
other hand, knowing that Bloggs belongs to group 4073 is not particularly
human-readable and will always require a join.

Plan (b) works well when there is a small and controllable number of Groups
records, so that the user (or the admin) does not have to keep choosing key
values for the FullName that are unique and meaningful. On the other hand,
you can get the membership information by simply looking at the Users table
and that may be helpful -- unless, of course, you are usually going to be
joining anyway, in order to see other fields in the Groups table. You don't
say much about what you mean by "Groups.etc". It should be noted that most
db engines will take longer to perform joins on long text fields than on
short ones or, particularly, on integer ones.

I frequently use a combination of the two, and provide a short text fields
(typically 5 chars) to allow sorting and quick querying, but it often gets
redesigned later to full numeric keys.

One point to note is that it's not nice to have PrimaryGroup and
SecondaryGroup:- whatever happened to TertiaryGroup and QuaternaryGroup and
so on? You say that a user is a member of "other groups" so there must be
more than one. I wonder if you wouln't be better off with a completely
separate table like

BelongsTo(*UserID, *GroupCode, IsPrimary)

in order to handle it properly.

HTH


Tim F