Table relationship
in [Programming SQL]
|
Prev: query question
Next: Need Help for a Join Query
From: shapper on 9 Jul 2010 19:39 On Jul 9, 8:59 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > shapper (mdmo...(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. But wouldn't that create a problem? I mean, delete the gender and leave invalid values on the Profile? Or GenderId in Profiles becomes null? > > Apart from that you need to review the casading properties, the model > makes sense to me. I just have something I am not sure: In Profiles and Professors tables the "Id" is both a PK and a FK. Should I name it "UserId" instead because it is also a FK? So in my entity I would have: "Profile.UserId" which seems less confusing then "Profile.Id" or not? Well, I can always see Profile.Id as the Id of the Profile which is always equal to the Id of the user ... Just seems confusing. In relation to PK I tend to have them User.Id instead of User.UserId which makes no sense when using an entity. But when a key is both a PK and a FK which would you name it? Thanks, Miguel
From: Erland Sommarskog on 10 Jul 2010 05:46 shapper (mdmoura(a)gmail.com) writes: > But wouldn't that create a problem? > I mean, delete the gender and leave invalid values on the Profile? > Or GenderId in Profiles becomes null? The default is NO_ACTION, which really means ON DELETE CALL_THE_POLICE. That is, if someeone tries to delete a gender in use, there will be an error. > In Profiles and Professors tables the "Id" is both a PK and a FK. > Should I name it "UserId" instead because it is also a FK? I think you should call it UserID everywhere for clarity. -- 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 13 Jul 2010 08:46
On Jul 10, 10:46 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > shapper (mdmo...(a)gmail.com) writes: > > But wouldn't that create a problem? > > I mean, delete the gender and leave invalid values on the Profile? > > Or GenderId in Profiles becomes null? > > The default is NO_ACTION, which really means ON DELETE CALL_THE_POLICE. > That is, if someeone tries to delete a gender in use, there will be an > error. > > > In Profiles and Professors tables the "Id" is both a PK and a FK. > > Should I name it "UserId" instead because it is also a FK? > > I think you should call it UserID everywhere for clarity. > > -- > 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 Erland, Thank you for the help! |