From: shapper on
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
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
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!
First  |  Prev  | 
Pages: 1 2 3 4
Prev: query question
Next: Need Help for a Join Query