Table relationship
in [Programming SQL]
|
Prev: query question
Next: Need Help for a Join Query
From: shapper on 6 Jul 2010 17:52 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
From: --CELKO-- on 6 Jul 2010 20:22 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. Please real DDL instead of narrative. Here is my guess at constraints to ensure that professors and studetns cannot have the same email address. CREATE TABLE Users (user_name VARCHAR(50) NOT NULL PRIMARY KEY, user_password VARCHAR(20) NOT NULL, email_address VARCHAR (255) NOT NULL email_type CHAR(1) NOT NULL CHECK (email_type IN ('P', 'S')), UNIQUE (email_address, email_type)); CREATE TABLE Professors (professor_name VARCHAR(50) NOT NULL PRIMARY KEY, cv_txt VARCHAR(8000 NOT NULL, city_name VARCHAR(20) NOT NULL, phone_nbr CHAR(10) NOT NULL, email_address VARCHAR (255) NOT NULL email_type CHAR(1) DEFAULT 'P' NOT NULL CHECK (email_type = 'P'), FOREIGN KEY (email_address, email_type) REFERENCES Users (email_address, email_type) ON UPDATE CASCADE ON DELETE CASCADE); CREATE TABLE Students (student_name VARCHAR(50) NOT NULL PRIMARY KEY, birth_date DATE NOT NULL, city_name VARCHAR(20) NOT NULL, email_address VARCHAR (255) NOT NULL email_type CHAR(1) DEFAULT 'S' NOT NULL CHECK (email_type = 'S'), FOREIGN KEY (email_address, email_type) REFERENCES Users (email_address, email_type) ON UPDATE CASCADE ON DELETE CASCADE);
From: shapper on 6 Jul 2010 20:46 On Jul 7, 1:22 am, --CELKO-- <jcelko...(a)earthlink.net> wrote: > 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. > > Please real DDL instead of narrative. Here is my guess at constraints > to ensure that professors and studetns cannot have the same email > address. > > CREATE TABLE Users > (user_name VARCHAR(50) NOT NULL PRIMARY KEY, > user_password VARCHAR(20) NOT NULL, > email_address VARCHAR (255) NOT NULL > email_type CHAR(1) NOT NULL > CHECK (email_type IN ('P', 'S')), > UNIQUE (email_address, email_type)); > > CREATE TABLE Professors > (professor_name VARCHAR(50) NOT NULL PRIMARY KEY, > cv_txt VARCHAR(8000 NOT NULL, > city_name VARCHAR(20) NOT NULL, > phone_nbr CHAR(10) NOT NULL, > email_address VARCHAR (255) NOT NULL > email_type CHAR(1) DEFAULT 'P' NOT NULL > CHECK (email_type = 'P'), > FOREIGN KEY (email_address, email_type) > REFERENCES Users (email_address, email_type) > ON UPDATE CASCADE > ON DELETE CASCADE); > > CREATE TABLE Students > (student_name VARCHAR(50) NOT NULL PRIMARY KEY, > birth_date DATE NOT NULL, > city_name VARCHAR(20) NOT NULL, > email_address VARCHAR (255) NOT NULL > email_type CHAR(1) DEFAULT 'S' NOT NULL > CHECK (email_type = 'S'), > FOREIGN KEY (email_address, email_type) > REFERENCES Users (email_address, email_type) > ON UPDATE CASCADE > ON DELETE CASCADE); Hello, I didn't create DDL code yet because I am still a bit confused about it. 1. My idea was to have on the USERS table a field USER_ID that is PK. Then on both PROFESSORS and STUDENTS tables I would have only a FK: USER_ID. Does this make sense? 2. All fields that are common to every user I would place them in USER table (for example email). Or maybe have the following tables: USERS > PROFILES > PROFESSORS > STUDENTS 3. If I have need to have the GENDER I would have it on USERS table on in case of approach 2 have it in PROFILE. Now in this case shouldn't GENDER be a Lookup table? Or maybe have what you used for email? I will post my DDL ... I just would like to clarify these issues so I can create it and have some feedback on it.
From: Erland Sommarskog on 7 Jul 2010 04:48 shapper (mdmoura(a)gmail.com) writes: > I didn't create DDL code yet because I am still a bit confused about > it. Celko sometimes behave like an automated robot that is not entirely able to understand the posts it responds to. > 1. My idea was to have on the USERS table a field USER_ID that is PK. > Then on both PROFESSORS and STUDENTS tables I would have only a > FK: USER_ID. > > Does this make sense? I think Joe Celko's suggestion makes sense. He we went one step further by adding this constraint on the email address. You may find that it shots over the target. Or may simply not be applicable to your real-world case which is about something else. > 2. All fields that are common to every user I would place them in USER > table (for example email). > Or maybe have the following tables: > USERS > PROFILES > PROFESSORS > > STUDENTS Not sure where the profiles com in here. In any case, the problem you describe is the typical supertypes and subtypes problem, and there are a couple of way to skin that cat. I would say which is the best depends on the situation. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: shapper on 7 Jul 2010 06:26
On Jul 7, 9:48 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > shapper (mdmo...(a)gmail.com) writes: > > I didn't create DDL code yet because I am still a bit confused about > > it. > > Celko sometimes behave like an automated robot that is not entirely > able to understand the posts it responds to. > > > 1. My idea was to have on the USERS table a field USER_ID that is PK. > > Then on both PROFESSORS and STUDENTS tables I would have only a > > FK: USER_ID. > > > Does this make sense? > > I think Joe Celko's suggestion makes sense. He we went one step further > by adding this constraint on the email address. You may find that it > shots over the target. Or may simply not be applicable to your real-world > case which is about something else. > > > 2. All fields that are common to every user I would place them in USER > > table (for example email). > > Or maybe have the following tables: > > USERS > PROFILES > PROFESSORS > > > STUDENTS > > Not sure where the profiles com in here. > > In any case, the problem you describe is the typical supertypes and > subtypes problem, and there are a couple of way to skin that cat. > I would say which is the best depends on the situation. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hello, 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 In relation to email constraint, all emails must be unique and I am testing that on my application. Does this make sense? Thank You, Miguel |