User table structure

[Steve_s]

Hi

I would like some help if possible with the following situation.

I have database which I would, at some point like to allow customers,
suppliers and our own staff access to the database. At present I have
tables for staff members of all three categories, however I feel that
there must be a way to just have users in one table, with someway of
identifying which category the user falls into. I understand how to
include a category field in each user record, but it then gets untidy
when I include which company customers and suppliers work for, in as
much as each user record would have a which customer company field AND
which supplier field in every record.

Am I being to rigid in my approach to reducing tables etc or is there
a standard approach to this type of situation?

Thanks in advance for any help anyone give me

Steve

[storrboy]

Re: User table structure


I don't know that there is a real 'standard', but I would suggest you
look at each of the things you addressed as separate entities. In my
experience, Customers and Vendors and kept together as 'Companies' (or
contacts or other name) and identified by a 'Vendor field or Customer
field or both. Users are people and are usually assigned to a 'group'.
If you are not using Access security, doing it this way all three
tables can be linked and not duplicate data. An example might be:

tableCompanies
----------------------
CompanyId
CompanyName
Phone
blah, blah

tableUsers
----------------
UserID
Password
UserName
GroupID
CompanyID
blah, blah

tableGroups
-----------------
GroupID
GroupName

The assignment of rights might depend on what they are and how you
plan to use them. One way might be to have a tableGroupRight s where
each row is a group and all other fields are various right and
permissions. Or the reverse, each row is a right with one field for
the groupID which can have duplicates. Each right then would be in a
list form in the table.If user permissions is prefered over groups,
the same logic can be used, but assign rights to the user instead of
the group.

[Steve_s]

Re: User table structure

On 27 Mar, 01:19, "storrboy" <storr...@sympa tico.cawrote:

I don't know that there is a real 'standard', but I would suggest you
look at each of the things you addressed as separate entities. In my
experience, Customers and Vendors and kept together as 'Companies' (or
contacts or other name) and identified by a 'Vendor field or Customer
field or both. Users are people and are usually assigned to a 'group'.
If you are not using Access security, doing it this way all three
tables can be linked and not duplicate data. An example might be:
>
tableCompanies
----------------------
CompanyId
CompanyName
Phone
blah, blah
>
tableUsers
----------------
UserID
Password
UserName
GroupID
CompanyID
blah, blah
>
tableGroups
-----------------
GroupID
GroupName
>
The assignment of rights might depend on what they are and how you
plan to use them. One way might be to have a tableGroupRight s where
each row is a group and all other fields are various right and
permissions. Or the reverse, each row is a right with one field for
the groupID which can have duplicates. Each right then would be in a
list form in the table.If user permissions is prefered over groups,
the same logic can be used, but assign rights to the user instead of
the group.

Thanks very much for your suggestions. I had kind of thought about the
companies table and user tables scenario, but not the groups table.
Having it explained this way has helped greatly, as it is re-assuring
that others would tackle the issue in this manner and helps to banish
the anti-confidence gremlins from the thought process!

Regards

Steve