Tips for Normalizing a Users Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tips for Normalizing a Users Table

Author

Topic

KenA
Starting Member

28 Posts

Posted - 2004-06-29 : 14:50:29

Hi. I´d like to save User´s data into a table called Users, but I have at least 2 types of Users and they will have different table fields according to its type. Ex.

========
Users
========
UserID
Username
Password
========

A user of type==User01 will use the info in the Users table, plus:
========
User01
========
Fullname
Email
Telephone
========

A user of type==User02 will use the info in the Users table, plus:
========
User02
========
CompanyName
Address
Telephone
Fax
========

I could use just 1 table (the Users table) and save info for both user types, but depending of the type of user I´m saving info, some fields will be left blank/null, so I believe that I need a way to to have separate tables to save the user details according to the user type and a main table where I save the UserID,Username and Password because those fields are common to any kind of user.
I would appreciate receiving some tips regarding these issues...
»»» Ken.A

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 15:32:32

And the answer is....yes

There are differences between logical and physical implementation.

Logical: The 2 tables...with the common data stored in the single table

Physical: Well that's where things get f=grey...the closer you can stay to the logical the better...my opinion...but there could be some BIG road blocks...

How much data...how many "splits" to you logically need to make...what is the expected level of transactions...lots of reasons

And what are the "types" anyway..please don't store 01 and 02 in the database..they must have some meaning...

Brett

8-)

KenA
Starting Member

28 Posts

Posted - 2004-06-29 : 15:51:18

Ok ... but I still need some design samples for those tables including PK and FK and the relationships.

»»» Ken.A

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-29 : 16:07:20

[code]

DROP TABLE myUsertype2
go

CREATE TABLE myUsertype2 (
myUserId char(18) NOT NULL,
LoginId char(18) NOT NULL,
CompanyName char(18) NOT NULL,
ect char(18) NOT NULL
)
go

ALTER TABLE myUsertype2
ADD PRIMARY KEY (myUserId)
go

DROP TABLE myUserType1
go

CREATE TABLE myUserType1 (
myUserId char(18) NOT NULL,
Email char(18) NULL
)
go

ALTER TABLE myUserType1
ADD PRIMARY KEY (myUserId)
go

DROP TABLE myUser99
go

CREATE TABLE myUser99 (
myUserId char(18) NOT NULL,
myUserEct char(18) NOT NULL,
myUserName char(18) NOT NULL,
myUserType char(18) NOT NULL
)
go

ALTER TABLE myUser99
ADD PRIMARY KEY (myUserId)
go

ALTER TABLE myUsertype2
ADD FOREIGN KEY (myUserId)
REFERENCES myUser99
go

ALTER TABLE myUserType1
ADD FOREIGN KEY (myUserId)
REFERENCES myUser99
go

[/code]

Brett

8-)

KenA
Starting Member

28 Posts

Posted - 2004-06-29 : 17:49:10

Thanks for the tip ... this also appear to be a good one: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/html/HowManysTooMany.asp

»»» Ken.A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources