One vs Many Tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

One vs Many Tables

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-08-18 : 19:20:01

Hello,

I have to save information about users on SQL tables.

For each user I have the following information:
1) Membership (username, createdDate, password, lastLogin, etc)
2) Profile data (Name, Email, Birthday, etc)
3) Professor data if the user is a professor (CurriculumVitae,
Comments, etc)
If a User is a Professor it needs to be related with Subjects
using ProfessorsSubjects.

My first approach, probably influenced by OOP (C#) is to use many
tables:

create table dbo.Users
(
Id int identity(1,1) not null,
Approved bit not null constraint DF_Users_Approved default(0),
Username nvarchar(100) not null,
-- Other membership columns
constraint PK_Users primary key clustered(Id)
)

create table dbo.Profiles
(
UserId int not null,
Birthday datetime null,
City nvarchar(100) null,
DistrictId int not null,
-- Other profile columns
constraint PK_Profiles primary key clustered(UserId)
)
NOTE: In Profiles table I am using a One to One relationship ... I
think it is correct

create table dbo.Professors
(
UserId int not null,
CurriculumVitae nvarchar(max) null,
-- Other professor columns
constraint PK_Professors primary key clustered(UserId)
)
NOTE: Then I will have ProfessorsSubjects as follows:

create table dbo.ProfessorsSubjects
(
UserId int not null,
SubjectId int not null,
constraint PK_ProfessorsSubjects primary key clustered(UserId,
SubjectId)
)

And and also the Roles table:

create table dbo.UsersRoles
(
UserId int not null,
RoleId int not null,
constraint PK_UsersRoles primary key clustered(UserId, RoleId)
)

OR alternatively I can join Users, Profiles and Professors tables
columns in a single table Users.
Then I would have UsersSubjects with records only for Professors and
UsersRoles.
Note that in Users tables CurriculumVitae and other Professors columns
would be null for non Professors.

How would you do this?

Thank You,
Miguel

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-19 : 07:34:20

http://en.wikipedia.org/wiki/First_normal_form

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources