Table relationship
in [Programming SQL]
|
Prev: query question
Next: Need Help for a Join Query
From: shapper on 8 Jul 2010 19:02 On Jul 8, 10:08 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > shapper (mdmo...(a)gmail.com) writes: > > Every user should have a profile. > > The reason why I have the USERS table and the PROFILE table is the > > following: > > > From project to project the USERS table contains everything needed for > > my authentication system and it is always the same. > > The profile table contains that info for the USER that can be > > different from project to project. > > For example, in project X I might need fields A, B and C and in > > project Y I might need fields A, B, .... O, P. > > > So what I need to change is the Profile table. > > The USERS table, its mappings, repositories, etc is always the same. > > I don't need to change it. > > > Does this make sense? > > I can see the practical reasoning behind it, but from a strict data- > modelling perspective it is not really kosher. Not a major violation, > but since there are no commit-time constraints in SQL Server, you > cannot set up any constraint to ensure that all rows in Users have a > matching row in Profiles. Wouldn't that be possible by using: 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 ) I removed the PK. Then Profile is just a "break" of table user ... Does this make sense? It is like "breaking" one table in two or not? In fact I think in ASP.NET Membership SQL tables they do something like this ...~ I can always have all fields (used in authentication system and the ones that are common to all users like City, Birthday, etc) in Users table. Basically get rid of Profile table: -- 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, 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_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 -- Professors create table dbo.Professors ( Id int identity not null, UserId int not null, CurriculumVitae nvarchar(max) null, Mobile nvarchar(20) null, Phone nvarchar(20) null constraint PK_Professors primary key clustered(Id) ) But I think here there would be a problem to. For a user I can have no record professors or one record in professors ... no more then one ... What approach would you use? Thanks, Miguel
From: Erland Sommarskog on 9 Jul 2010 05:19 shapper (mdmoura(a)gmail.com) writes: >> I can see the practical reasoning behind it, but from a strict data- >> modelling perspective it is not really kosher. Not a major violation, >> but since there are no commit-time constraints in SQL Server, you >> cannot set up any constraint to ensure that all rows in Users have a >> matching row in Profiles. > > Wouldn't that be possible by using: > > 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 > ) > > I removed the PK. Then Profile is just a "break" of table user ... > Does this make sense? It is like "breaking" one table in two or not? That makes even less sense. Now there can be multiple profiles for the same user. If you are to have two tables, you need the PK *and* you need an FK to Users, so you don't get profiles without users- > I can always have all fields (used in authentication system and the > ones that are common to all users like City, Birthday, etc) in Users > table. Basically get rid of Profile table: That would be the right thing do to from a pure data-modelling perspective. Thereby not saying that you desire to have a table which is static from project to project should be ignored. Having two tables may indeed be the best practical solution. > create table dbo.Professors > ( > Id int identity not null, > UserId int not null, > CurriculumVitae nvarchar(max) null, > Mobile nvarchar(20) null, > Phone nvarchar(20) null > constraint PK_Professors primary key clustered(Id) > ) > > But I think here there would be a problem to. > > For a user I can have no record professors or one record in > professors ... no more then one ... The PK in Profressors should be UserId. And an FK to Users. Here it is OK to use a separte table, since this only apply to a subset of the users. You could have all columns in Users, and a ton of constraints say that this column is mandatory for professors and must be NULL for everyone else, but that gets messy. -- 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 9 Jul 2010 07:25 On Jul 9, 10:19 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > shapper (mdmo...(a)gmail.com) writes: > >> I can see the practical reasoning behind it, but from a strict data- > >> modelling perspective it is not really kosher. Not a major violation, > >> but since there are no commit-time constraints in SQL Server, you > >> cannot set up any constraint to ensure that all rows in Users have a > >> matching row in Profiles. > > > Wouldn't that be possible by using: > > > 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 > > ) > > > I removed the PK. Then Profile is just a "break" of table user ... > > Does this make sense? It is like "breaking" one table in two or not? > > That makes even less sense. Now there can be multiple profiles for > the same user. If you are to have two tables, you need the PK *and* > you need an FK to Users, so you don't get profiles without users- > > > I can always have all fields (used in authentication system and the > > ones that are common to all users like City, Birthday, etc) in Users > > table. Basically get rid of Profile table: > > That would be the right thing do to from a pure data-modelling perspective. > Thereby not saying that you desire to have a table which is static from > project to project should be ignored. Having two tables may indeed be > the best practical solution. > > > create table dbo.Professors > > ( > > Id int identity not null, > > UserId int not null, > > CurriculumVitae nvarchar(max) null, > > Mobile nvarchar(20) null, > > Phone nvarchar(20) null > > constraint PK_Professors primary key clustered(Id) > > ) > > > But I think here there would be a problem to. > > > For a user I can have no record professors or one record in > > professors ... no more then one ... > > The PK in Profressors should be UserId. And an FK to Users. Here it > is OK to use a separte table, since this only apply to a subset of > the users. You could have all columns in Users, and a ton of constraints > say that this column is mandatory for professors and must be NULL > for everyone else, but that gets messy. > > -- > 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, I recreated my code for the database following what seems logic to me: 1 - A USER can have many ROLES 2 - A USER can have one PROFILE. 3 - A USER can be a PROFESSOR or not. 4 - A USER PROFILE can have only one DISTRICT. 5 - A USER PROFILE can have only one GENDER. 6 - A USER PROFESSOR can have many LEVELS. 7 - A USER PROFESSOR can have many SUBJECTS. Then I created an Entity Framework 4 model from it. My DDL seems logic and my EF4 model seems to be according to it. I placed all constraints next to each table to be easier to see. -- Districts create table dbo.Districts ( Id int identity not null, [Name] nvarchar(100) not null, constraint Districts_PK primary key clustered(Id) ) -- Districts -- Genders create table dbo.Genders ( Id int identity not null, [Name] nvarchar(20) not null, constraint Genders_PK primary key clustered(Id) ) -- Genders -- Levels create table dbo.Levels ( Id int identity not null, [Name] nvarchar(20) not null, constraint Levels_PK primary key clustered(Id) ) -- Levels -- Roles create table dbo.Roles ( Id int identity not null, [Name] nvarchar(40) not null, constraint Roles_PK primary key clustered(Id) ) -- Roles -- Subjects create table dbo.Subjects ( Id int not null, [Name] nvarchar(100) null, constraint Subjects_PK primary key clustered(Id) ) -- Subjects -- Users create table dbo.Users ( Id int identity not null, Approved bit not null constraint Users_Approved_DF 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 Users_Locked_DF 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 Users_Username_U unique, constraint Users_U primary key clustered(Id) ) -- Users -- Profiles create table dbo.Profiles ( Id 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 Users_Newsletter_DF default(0) constraint Profiles_PK primary key clustered(Id), constraint Profiles_Users_FK foreign key(Id) references dbo.Users(Id) on delete cascade on update cascade, constraint Profiles_Districts_FK foreign key(DistrictId) references dbo.Districts(Id) on delete cascade on update cascade, constraint Profiles_Genders_FK foreign key(GenderId) references dbo.Genders(Id) on delete cascade on update cascade ) -- Professors create table dbo.Professors ( Id int not null, CurriculumVitae nvarchar(max) null, Mobile nvarchar(20) null, Phone nvarchar(20) null constraint Professors_PK primary key clustered(Id), constraint Professors_Users_FK foreign key(Id) references dbo.Users(Id) on delete cascade on update cascade ) -- ProfessorsLevels create table dbo.ProfessorsLevels ( ProfessorId int not null, LevelId int not null, constraint ProfessorsLevels_PK primary key clustered(ProfessorId, LevelId), constraint ProfessorsLevels_Professors_FK foreign key(ProfessorId) references dbo.Professors(Id) on delete cascade on update cascade, constraint ProfessorsLevels_Levels_FK foreign key(LevelId) references dbo.Levels(Id) on delete cascade on update cascade ) -- ProfessorsLevels -- ProfessorsSubjects create table dbo.ProfessorsSubjects ( ProfessorId int not null, SubjectId int not null, constraint ProfessorsSubjects_PK primary key clustered(ProfessorId, SubjectId), constraint ProfessorsSubjects_Professors_FK foreign key(ProfessorId) references dbo.Professors(Id) on delete cascade on update cascade, constraint ProfessorsSubjects_Subjects_FK foreign key(SubjectId) references dbo.Subjects(Id) on delete cascade on update cascade ) -- ProfessorsSubjects -- UsersRoles create table dbo.UsersRoles ( UserId int not null, RoleId int not null, constraint UsersRoles_PK primary key clustered(UserId, RoleId), constraint UsersRoles_Users_FK foreign key(UserId) references dbo.Users(Id) on delete cascade on update cascade, constraint UsersRoles_Roles_FK foreign key(RoleId) references dbo.Roles(Id) on delete cascade on update cascade ) -- UsersRoles What do you think? Thank You, Miguel
From: shapper on 9 Jul 2010 10:41 On Jul 9, 12:25 pm, shapper <mdmo...(a)gmail.com> wrote: > On Jul 9, 10:19 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > > > > > > shapper (mdmo...(a)gmail.com) writes: > > >> I can see the practical reasoning behind it, but from a strict data- > > >> modelling perspective it is not really kosher. Not a major violation, > > >> but since there are no commit-time constraints in SQL Server, you > > >> cannot set up any constraint to ensure that all rows in Users have a > > >> matching row in Profiles. > > > > Wouldn't that be possible by using: > > > > 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 > > > ) > > > > I removed the PK. Then Profile is just a "break" of table user ... > > > Does this make sense? It is like "breaking" one table in two or not? > > > That makes even less sense. Now there can be multiple profiles for > > the same user. If you are to have two tables, you need the PK *and* > > you need an FK to Users, so you don't get profiles without users- > > > > I can always have all fields (used in authentication system and the > > > ones that are common to all users like City, Birthday, etc) in Users > > > table. Basically get rid of Profile table: > > > That would be the right thing do to from a pure data-modelling perspective. > > Thereby not saying that you desire to have a table which is static from > > project to project should be ignored. Having two tables may indeed be > > the best practical solution. > > > > create table dbo.Professors > > > ( > > > Id int identity not null, > > > UserId int not null, > > > CurriculumVitae nvarchar(max) null, > > > Mobile nvarchar(20) null, > > > Phone nvarchar(20) null > > > constraint PK_Professors primary key clustered(Id) > > > ) > > > > But I think here there would be a problem to. > > > > For a user I can have no record professors or one record in > > > professors ... no more then one ... > > > The PK in Profressors should be UserId. And an FK to Users. Here it > > is OK to use a separte table, since this only apply to a subset of > > the users. You could have all columns in Users, and a ton of constraints > > say that this column is mandatory for professors and must be NULL > > for everyone else, but that gets messy. > > > -- > > 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, > > I recreated my code for the database following what seems logic to me: > > 1 - A USER can have many ROLES > 2 - A USER can have one PROFILE. > 3 - A USER can be a PROFESSOR or not. > 4 - A USER PROFILE can have only one DISTRICT. > 5 - A USER PROFILE can have only one GENDER. > 6 - A USER PROFESSOR can have many LEVELS. > 7 - A USER PROFESSOR can have many SUBJECTS. > > Then I created an Entity Framework 4 model from it. > My DDL seems logic and my EF4 model seems to be according to it. > I placed all constraints next to each table to be easier to see. > > -- Districts > create table dbo.Districts > ( > Id int identity not null, > [Name] nvarchar(100) not null, > constraint Districts_PK primary key clustered(Id) > ) -- Districts > > -- Genders > create table dbo.Genders > ( > Id int identity not null, > [Name] nvarchar(20) not null, > constraint Genders_PK primary key clustered(Id) > ) -- Genders > > -- Levels > create table dbo.Levels > ( > Id int identity not null, > [Name] nvarchar(20) not null, > constraint Levels_PK primary key clustered(Id) > ) -- Levels > > -- Roles > create table dbo.Roles > ( > Id int identity not null, > [Name] nvarchar(40) not null, > constraint Roles_PK primary key clustered(Id) > ) -- Roles > > -- Subjects > create table dbo.Subjects > ( > Id int not null, > [Name] nvarchar(100) null, > constraint Subjects_PK primary key clustered(Id) > ) -- Subjects > > -- Users > create table dbo.Users > ( > Id int identity not null, > Approved bit not null constraint Users_Approved_DF 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 Users_Locked_DF 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 Users_Username_U unique, > constraint Users_U primary key clustered(Id) > ) -- Users > > -- Profiles > create table dbo.Profiles > ( > Id 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 Users_Newsletter_DF default(0) > constraint Profiles_PK primary key clustered(Id), > constraint Profiles_Users_FK foreign key(Id) references dbo.Users(Id) > on delete cascade on update cascade, > constraint Profiles_Districts_FK foreign key(DistrictId) references > dbo.Districts(Id) on delete cascade on update cascade, > constraint Profiles_Genders_FK foreign key(GenderId) references > dbo.Genders(Id) on delete cascade on update cascade > ) > > -- Professors > create table dbo.Professors > ( > Id int not null, > CurriculumVitae nvarchar(max) null, > Mobile nvarchar(20) null, > Phone nvarchar(20) null > constraint Professors_PK primary key clustered(Id), > constraint Professors_Users_FK foreign key(Id) references > dbo.Users(Id) on delete cascade on update cascade > ) > > -- ProfessorsLevels > create table dbo.ProfessorsLevels > ( > ProfessorId int not null, > LevelId int not null, > constraint ProfessorsLevels_PK primary key clustered(ProfessorId, > LevelId), > constraint ProfessorsLevels_Professors_FK foreign key(ProfessorId) > references dbo.Professors(Id) on delete cascade on update cascade, > constraint ProfessorsLevels_Levels_FK foreign key(LevelId) references > dbo.Levels(Id) on delete cascade on update cascade > ) -- ProfessorsLevels > > -- ProfessorsSubjects > create table dbo.ProfessorsSubjects > ( > ProfessorId int not null, > SubjectId int not null, > constraint ProfessorsSubjects_PK primary key > clustered(ProfessorId, SubjectId), > constraint ProfessorsSubjects_Professors_FK foreign key(ProfessorId) > references dbo.Professors(Id) on delete cascade on update cascade, > constraint ProfessorsSubjects_Subjects_FK foreign key(SubjectId) > references dbo.Subjects(Id) on delete cascade on update cascade > ) -- ProfessorsSubjects > > -- UsersRoles > create table dbo.UsersRoles > ( > UserId int not null, > RoleId int not null, > constraint UsersRoles_PK primary key clustered(UserId, RoleId), > constraint UsersRoles_Users_FK foreign key(UserId) references > dbo.Users(Id) on delete cascade on update cascade, > constraint UsersRoles_Roles_FK foreign key(RoleId) references > dbo.Roles(Id) on delete cascade on update cascade > ) -- UsersRoles > > What do you think? > > Thank You, > Miguel By the way, I mapped this database to EF4 and got the following: http://www.flyondreams.net/Lab/EF4.jpg Let me know if this makes sense ... Would you improve it somehow? For example, I am not sure if in tables Professors and Profiles since they FK is the same as PK that I should name it UserId ... Or not? Well, this is where I am so far ...
From: Erland Sommarskog on 9 Jul 2010 15:59
shapper (mdmoura(a)gmail.com) writes: > -- Profiles > create table dbo.Profiles > ( > Id 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 Users_Newsletter_DF default(0) > constraint Profiles_PK primary key clustered(Id), > constraint Profiles_Users_FK foreign key(Id) references dbo.Users(Id) > on delete cascade on update cascade, > constraint Profiles_Districts_FK foreign key(DistrictId) references > dbo.Districts(Id) on delete cascade on update cascade, > constraint Profiles_Genders_FK foreign key(GenderId) references > dbo.Genders(Id) on delete cascade on update cascade > ) Ahem, are you sure on all those ON DELETE CASCADE? If some decides to delete a gender, should we then delete all profiles with that gender *and* keep the users? It does make sense to have ON DELETE CASCADE for the FK on Users; if the user is deleted, the profile should go with it. But ON DELETE CASCADE is rarely a good thing for references to lookup tables like Districts or Genders. Apart from that you need to review the casading properties, the model makes sense to me. -- 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 |