Doubts in how to organize User information and its User Accounts 1

[Entrep]

Hello,

I was thinking to have a main 'Users' table with a few common data such as ‘StartDate’,
‘KindOfService’, ‘DelegationsServiceIncluded’, and ‘CurrentState’. And all the other User information in separate spread tables linked to main Users table, tables such as ‘UsersCompanyName’, ‘UsersSocialSecurityNum’,
‘UsersActivity’, ‘UsersAddresses’, ‘UsersTelFax’,
‘UsersEmail’, ‘UsersWebAddr’, ‘UsersContactName’,
‘UsersContactSurname’, ‘UsersContactHours’,
‘UsersJobPosition’, and so on.

On the other hand, create a Users Data Access table linked to main Users table through UserNumber field. That table would contain User Name, Password, Current State, and Access Category field (which determine the account access hierarchy: Administrator, Supervisor, or Technical).
User information tables mentioned firstly, will contain a Foreing Key of this table (‘UsersDataAccess’) so that all users accounts have its contact information and the rest of the data of its Company.

But the ‘problem’ will be when an additional user account is added from the same Company, then I will have to ‘refill’ fields of users information tables with possibly the same content of other users accounts of the same Company (like Company Name, Social Security Number, Activity, Address,..). How can I avoid this? Or this repeated information doesn’ t matter..

Note: Not in all cases this ‘problem’ will happen, because a user which is a Company can own other Companies, so, can have other user accounts in our website app with different Company Name, Social Security Number, Activity, Address,.. Regardless of those users belong to the same Company.

Thank you

 

[ousoonerjoe]

Take a look at the following. By using 3 tables, you can have individual Users, individual Companies, and the CompanyUsers will allow those to be tied together as needed.

CREATE TABLE Company
CompanyID INT IDENTITY(1,1)
CompanyName VARCHAR(50)
Addr1 VARCHAR(100)
Addr2 VARCHAR(100)
Addr3 VARCHAR(100)
City VARCHAR(50)
State VARCHAR(2)
Zip VARCHAR(10)
--any other items that would be Company oriented.

CREATE TABLE CompanyUsers
CompanyUsersID INT IDENTITY(1,1)
CompanyID INT
UserID INT

CREATE TABLE Users
UserID INT IDENTITY(1,1)
FName VARCHAR(50)
LName VARCHAR(50)
Addr1 VARCHAR(100)
Addr2 VARCHAR(100)
Addr3 VARCHAR(100)
City VARCHAR(50)
State VARCHAR(2)
Zip VARCHAR(10)
StartDate DATETIME
JobTitle VARCHAR(50)
KindOfService VARCHAR(50)
DelegationsServiceIncluded VARCHAR(50)
SSN VARCHAR(11)
UsersActivity VARCHAR(50)
Phone VARCHAR(20)
Fax VARCHAR(20)
Email VARCHAR(75)
ContactHours VARCHAR(50)
--any other items that would be Company oriented.

If you want to break it down even more, Create an Address Table. If you need to allow for Parent Companies owning other Companies, then Add a ParentCompany Table just like the CompanyUsers table. This will allow for growth and should prevent most of your redundancy of data.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Entrep]

Your idea looks simple and clear, I like it at a first glance.

But I would like to clarify some questions which I still have:

The repeated data ‘problem’ mentioned in my post will still happen in your design suggested, isn’ t it? But it will be minimum, if I understand your comment This will allow for growth and should prevent most of your redundancy of data.

I guess my ‘UsersDataAccess’ table is suitable in you suggested design, but this time would be linked to your Users table PK. Isn’ t it? You would use that table (UsersDataAccess)?

In my business model most of the Companies mentioned, but not all, will be organized with delegations (a central office and different stores in different places), so, some users will work in the central office and some others in its delegations/stores.
I was thinking in introduce that information in the DB so that I can know which delegations/stores the users belongs to, and also I will eliminate some fields with repeated information in Users table because a group of users share the same delegation data (Address, City, State, Zip, Phone, Fax, etc..). How you would introduce that table or information in your suggested design?


Thank you for your design idea, it clarifies me much more the DB structure and seems more logical.

 

[ousoonerjoe]

An Address Table would reduce a lot of the redundant addresses.

CREATE TABLE [dbo].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[Addr1] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Addr2] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Addr3] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Addr4] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[City] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[State] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Zip] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

Then add AddressID to both the Users and Company Tables. This will allow multiple people to have the same address and not have the redundancy in the DB.

You will have a little redundancy, but that is mostly do to the users not checking to see if an address or a curtain piece of information is already in the system. This holds true for almost any database (A fight between IT and Users that will probably never end).

UsersDataAccess should be linked to the Users Table, yes. You want to set access permissions at the most specific level. For example, Company Bob's Parts has 4 employees. They fire one of the employees. You want to be able to turn off that specific individual access, not all of Bob's Parts. But with the CompanyUsers table, you can do a blanket update to ALL users in that company. Probably should add a BIT field to the Users table "Terminated" to indicate this person no longer works for the company and therefor should no longer have access regardless of permission levels. By adding that field, you can still do blanket updates and not have to go back and pick out the Users that were termed.

Hope this helps you see how all the tables relate and tie together.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.