Just my opinion, but I prefer numeric keys that the users *can't*
see, to alpha keys that the users can see:
1. numeric keys are faster
2. using numeric keys that the user can't see allows you
to open up(for UPDATE) ALL the fields that the user can see
including a field like School Name.
Problem with alpha School Name: If you allow the users to
change the school name, you'd have to change *ALL* occurrences
of the old school name to the new school name - in all
tables/rows that reference the old school name - could be
a big programming effort. If you don't allow the users to
change the school name, they might be unhappy - or maybe you
allow users to submit a form saying they want to change school
name from a to b, and you write a program which goes through
the database changing every occurrence of a to b. Again, a lot
of work, and users who are probably unhappy.
Solution: Instead of having the School Name be your primary
key, have a field called school_id that the users can't see
or update - once assigned to a school, that school_id is
the primary key to that school's info forever. If another
table/record needs to refer to a particular school, you
store the school_id in that table/record, instead of storing
the school name. Since the users can never update the
school_id, you don't have to worry about writing extra code
to update all the occurrences of a to b - you can then allow
users to update the school name field without affecting the
key - the school name is just another data field.
This may not be the best example for me to demonstrate why I
think numeric keys are better - maybe in this case the School
Name never changes for any of the schools. But if it could
change, then numeric keys could be the answer.
MySQL auto-increment fields are great for this, and Oracle has
a similar type of field called a "sequence". Where it makes
sense, my preference is to use an auto-increment INTEGER field as
the primary key to a table. Keeping the numeric keys used for
basic retrieval separate from the user viewable/updatable fields
makes for a smaller, less-complex, and more flexible program.
Again, just my opinion.
HTH.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
John Jensen [[EMAIL PROTECTED]] wrote:
> Think of the tables in your database as linked sets.
>
> I have a college database where school description as info associated
> in three other tables, allowing one school description, one or more
> contacts, requirements for one or more programs, and each program has
> one or more degrees associated, in the last table.
>
> In each table, the School name is replicated, serving as the primary
> key in only the first. The primary key of the second is replicated in
> the third, and so on. Only degrees have a number as the key, with
> School name and the key of the previous table entered as fields, to
> link everything together.
>
> How you use tables will be defined by how your data needs to be
> organized. Understanding your data needs is your first step.
>
> On 5 Feb 2001, at 10:55, Chris Toth wrote:
>
> >
> >
> > I'm having a extremely hard time grasping the concept of multiple
> > tables. So far, I've been using just one table when designing a
> > database. But now I have to design a database for a trouble-ticket
> > system for our department. I've written out the design of the tables,
> > but the part I don't understand is how the tables relate to each
> > other. Do I need to use foreign keys? But if I do, I thought MySQL
> > didn't support foreign keys?
> >
> > BTW, I've read most of the O'rielly mSQL/MySQL book and couldn't find
> > my answers.
> >
> > Just in case it matters, I'm going to have one table full of faculty
> > info, one of staff info, one for the trouble ticket itself, and
> > possibly one for actions performed on the trouble ticket.
> >
>
>
> John Jensen
> 520 Goshawk Court
> Bakersfield, CA 93309
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
