Table relationship
in [Programming SQL]
|
Prev: query question
Next: Need Help for a Join Query
From: Erland Sommarskog on 7 Jul 2010 17:07 shapper (mdmoura(a)gmail.com) writes: > 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. Judging from the table definitions, the same user can have more than one profile? (And more than one birthday?) > I can have more then two roles (Professors, Students, Parents, ...) > and add a few more in the future. And a user can have several roles as testified by the UsersRoles table. > 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, ...) It's probably the best in the long run. -- 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 20:45 On Jul 7, 10:07 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > shapper (mdmo...(a)gmail.com) writes: > > 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. > > Judging from the table definitions, the same user can have more than > one profile? (And more than one birthday?) No, a user can have only one profile. So you say I should have only: 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) ) Does this make sense? And can I set UserId as PK in profile table? I think I should have always a PK on a table ... not? More then one birthday? No just one ... So because I decided to have the birthday for all users I placed that field on Profile ... Am I missing something? > > I can have more then two roles (Professors, Students, Parents, ...) > > and add a few more in the future. > > And a user can have several roles as testified by the UsersRoles table. Yes, for example a Professor can be a Collaborator to ... I think my tables are ok for that but if you see something wrong ... > > > 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, ...) > > It's probably the best in the long run. > > -- > 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
From: Erland Sommarskog on 8 Jul 2010 06:51 shapper (mdmoura(a)gmail.com) writes: > No, a user can have only one profile. So you say I should have only: > > 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) > ) > > Does this make sense? And can I set UserId as PK in profile table? > I think I should have always a PK on a table ... not? UserId should then be a foriegn key to the Users table. But can there be a user without a profile? That is, why have this table at all? Also, I am a little curious: if I have a profile, I must specify my birthday (or make something up), but I don't have to specify my city? Maybe there are good reasons for why some columns are nullable and others not, but I had to ask. -- 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 8 Jul 2010 08:59 On Jul 8, 11:51 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > UserId should then be a foriegn key to the Users table. But can there > be a user without a profile? That is, why have this table at all? No. 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? Any suggestion is welcome. > Also, I am a little curious: if I have a profile, I must specify my > birthday (or make something up), but I don't have to specify my city? > Maybe there are good reasons for why some columns are nullable and > others not, but I had to ask. Yes, I am asking District. I am more interested on the District because it is normalized so I can have some queries on it and the City which is impossible to normalize. I use this field to get an idea of the regions of all users ... City is an extra but not required. Is this what you mean? Thank You, Miguel
From: Erland Sommarskog on 8 Jul 2010 17:08
shapper (mdmoura(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. -- 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 |