Re: Storing/referencing users

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1149356671.433831_at_bubbleator.drizzle.com...
> Dave wrote:
>> 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.
>
> It is best practice to state the version of the database and the
> business rules.

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.

> Is each individual user logging in with the same
> userid and password or does each one have their own?

Each 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?

Arbitrary.

> How do other applications in the organization identify users such
> that you can exchange data between systems. Where do user names come
> from?

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

> Could their be two Bob Joneses? How are names to be maintained?

System admins will maintain user accounts.

> The simple answer is that there is no "right" way.

Thanks for the answer. Received on Sun Jun 04 2006 - 05:33:39 CDT