Is it best practise to:

1) Store users in a User table, with a numeric UserID as primary key, and
reference these users in other tables using that UserID as a foreign key?
Also, this would require an additional username field.

or

2) Store users in a User table, with a text Username as primary key, and
reference these users in other tables using the Username as a foreign key?

TIA.

Dave wrote:
It is best practice to state the version of the database and the
business rules. Is each individual user logging in with the same
userid and password or does each one have their own? Do users have
a unique surrogate key such as an employee id number or are you
talking about creating a new purely arbitrary numbering system?
How do other applications in the organization identify users such
that you can exchange data between systems. Where do user names come
from? Could their be two Bob Joneses? How are names to be maintained?

The simple answer is that there is no "right" way.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan" <damorgan (AT) psoug (DOT) org> wrote

PostgreSQL and I'm developing a financial application, which will involve
reports which return results on a per-user basis. Some of the tables will
contain 100,000+ rows.

Each have their own.

Arbitrary.

Traditionally first letter of forename plus surname e.g. "bjones", these are
assigned by system admins.

System admins will maintain user accounts.

Thanks for the answer.

Dave wrote:

Then it isn't an Oracle problem so you should not have posted to
comp.databases.oracle.misc.

However my question still stand no matter the database. And if each
individual user has their own person userid and password your table
is, at least in an Oracle context, a superfluous waste of time and
effort reinventing the wheel. Too bad you aren't working in a "real"
database.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"Dave" <dave (AT) gmailNOSPAM (DOT) com> wrote

In general, the second option is better for a new single system, as long
as the database handles string keys efficiently. In fact, for an
Internet-connected appliction, it's probably best to use an e-mail address
as the primary key. However, it sounds like you have a more specific
situation...

It sounds like you're trying to restrict access based on existing accounts
maintained elsewhere in your organization. Postgres supports Kerberos,
IDENT and PAM methods for external authentication, and Java has classes
(e.g. under javax.naming.ldap) to allow queries against LDAP servers. Even
if you do have to run a script to import the NIS 'passwd' map into a
Postgres table, you should probably use the UNIX user name as the relevant
key in your Authorization and Accounting tables. If you don't already have
a central directory of users, your organization has other problems...

DLL