Table relationship - 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

Table relationship

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-07-06 : 17:54:38

Hello,

I have a table, USERS, where I hold the Username, Password and Email
of all users.

Each user can be a student or a professor.

For a student I need 3 more fields: NAME, BIRTHDAY and CITY

For professor I need 4 more fields: NAME, CV, CITY, PHONE.

Should I add all these fields to USERS table and leave the ones not
used for a Professor or Student empty?

Or maybe use another tables?
What would be the better approach for this?

Thanks,
Miguel

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-06 : 22:06:12

I prefer you must separate the information of the player and its position..

to be able to enhance your database design

read some article about normalization and denormalization..

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-07 : 01:33:57

here is the simple wikipedia text on database normalization: http://en.wikipedia.org/wiki/Database_normalization

All six normalization forms are relatively simple to understand, but sometimes hard to implement. so be sure to know your data and your relation model before you continue.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-07-07 : 06:29:06

Let me explain better and now posting my DDL.

The information to each user related to its subscription is on USERS table (Username, Password, Email, Approved, etc)

Information about each user (Name, Birthday, etc) is in Profiles table.

I can have more then two roles (Professors, Students, Parents, ...) and add a few more in the future.

Most roles have common fields but a few like Professors need extra fields. So for that I create a table with the role name for the roles where that is needed (Professors, ...)

My DDL:


-- Users
create table dbo.Users
(
 Id int identity not null,
 Approved bit not null constraint DF_Users_Approved default(0),
 Comment nvarchar(2000) null,
 Created datetime not null,	
 Email nvarchar(200) not null,	
 LastLock datetime not null,
 LastLogin datetime not null,		
 LastPasswordChange datetime not null,		
 LastReset datetime not null,
 Locked bit not null constraint DF_Users_Locked default(0),
 [Password] varbinary(200) not null,
 PasswordAttemptCount int not null,
 PasswordAttemptStart datetime not null,		
 Salt varbinary(800) not null,
 Updated datetime not null,
 Username nvarchar(40) not null constraint U_Users_Username unique,
  constraint PK_Users primary key clustered(Id)
) -- Users

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

-- Profiles
create table dbo.Profiles
(
 Id int identity not null,
 UserId int not null,
 Birthday datetime not null,
 City nvarchar(100) null,
 DistrictId int not null,
 GenderId int not null, 
 [Name] nvarchar(100) not null,
 Newsletter bit not null constraint DF_Users_Newsletter default(0),
 Phone nvarchar(20) null,
		constraint PK_Profiles primary key clustered(Id)
)

-- Professors
create table dbo.Professors
(
 Id int identity not null,
 ProfileId int not null,
 CurriculumVitae nvarchar(max) null,	
 Mobile nvarchar(20) null,
 Phone nvarchar(20) null
  constraint PK_Professors primary key clustered(Id)
)

-- Genders
create table dbo.Genders
(
 Id int identity not null,
 [Name] nvarchar(10) not null,
  constraint PK_Genders primary key clustered(Id)
) -- Genders

Does this make sense? What do you think?

Thank You,
Miguel

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-07 : 08:10:56

Maybe you can ignore the gender Table

there are 3 method of Data Integrity

make use of the domain integrity...
see this stuff it might help...
http://www.mssqlcity.com/Articles/General/using_constraints.htm

A maze make you much more better

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 09:22:31

I think the Genders table is fine but I would have a table Communication to hold Phone, Mobilphone, eMail etc.
Maybe like this:

ID int
Com_Type tinyint -- 1=Phone, 2=Mobilphone, 3=eMailaddress, 4=Fax and so on
Com_Value varchar(255) -- to hold the needed number or Mailadress
Com_Prio tinyint -- to say which comChannel is to prefer if there are more than one

No, you're never too old to Yak'n'Roll if you're too young to die.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-07-07 : 20:48:24

A user can have only one profile.
Should I have the following instead:


create table dbo.Profiles 
( 
  UserId int not null, 
  Birthday datetime not null, 
  City nvarchar(100) null, 
  DistrictId int not null, 
  GenderId int not null, 
  [Name] nvarchar(100) not null, 
  Newsletter bit not null constraint DF_Users_Newsletter default(0), 
  Phone nvarchar(20) null, 
    constraint PK_Profiles primary key clustered(UserId) 
)

I dropped the ProfileId and made the UserId as PK ... I am not sure if I can do this ...

What do you think?

Thanks,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources