advice on model
-
hi, i have this little model defined and would like hear something a could improve:
in essence it defines classes and groups. each class can have one or more group and each group will have a group of students later.
any advice's you can give ?
create table ClassTypes(
Id bigint not null identity(1,1),
Name nvarchar(25) not null,
Description nvarchar(250) null default '',
Active bit default 1,
Deleted bit default 0,
CONSTRAINT [PK_ClassTypes] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
create table Classes
(
ClassId bigint identity(1,1),
ClassTypeId bigint not null,
PlanStudyId bigint not null,
CurricularUnitId bigint not null,
AcademicYearId bigint not null,
Name nvarchar(25) not null,
Description nvarchar(250) not null default '',
StartDate datetime not null,
EndDate datetime not null,
Deleted bit not null default 0,
CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED ([ClassId] ASC),
CONSTRAINT [FK_Classes_ClassTypes] FOREIGN KEY ([ClassTypeId]) REFERENCES [dbo].[ClassTypes] ([Id]),
CONSTRAINT [FK_Classes_PlanStudiesCurricularUnitsAcademicYears] FOREIGN KEY ([PlanStudyId], [CurricularUnitId], [AcademicYearId]) REFERENCES [dbo].... ON DELETE CASCADE
)
GO
create table ClassGroupTypes
(
Id bigint not null identity(1,1),
Name nvarchar(25) not null,
Description nvarchar(250) null default '',
Active bit default 1,
Deleted bit default 0,
CONSTRAINT [PK_ClassGroupTypes] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO
create table ClassGroups
(
GroupId bigint not null identity(1,1),
ClassId bigint not null,
ClassGroupTypeId bigint not null,
Name nvarchar(25) not null,
Description nvarchar(250) not null default '',
StartDate datetime not null,
EndDate datetime not null,
Deleted bit not null default 0,
CONSTRAINT [PK_ClassGroups] PRIMARY KEY CLUSTERED ([GroupId] ASC),
CONSTRAINT [FK_ClassesGroups_Classes] FOREIGN KEY ([ClassId]) REFERENCES [dbo].[Classes] ([ClassId]),
CONSTRAINT [FK_ClassesGroups_ClassGroupTypes] FOREIGN KEY ([ClassGroupTypeId]) REFERENCES [dbo].[ClassGroupTypes] ([Id]),
)
GO
create table ClassGroupUsers
(
ClassGroupId bigint not null,
UserId bigint not null,
CONSTRAINT [PK_ClassGroupUsers] PRIMARY KEY CLUSTERED ([ClassGroupId] ASC, [UserId] ASC),
CONSTRAINT [FK_ClassesGroupUsers_ClassGroups] FOREIGN KEY ([ClassGroupId]) REFERENCES [dbo].[ClassGroups] ([ClassId]),
CONSTRAINT [FK_ClassesGroupUsers_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id])
)
-
"classes" ... "students"
Hmmm, lemme guess. Homework assignment?
If your teacher didn't manage to explain normalization well enough, then perhaps I can help you. Go to Pluralsight.com, sign up for a free trial account, and go watch my course on relational database design. You can skip most of the introduction (that is mostly a historic perspective), but the rest might be relevant. The most important modules to watch are the two "basic normalization" modules.
(The very short synopsis of what you'll learn is to not jump straight into designing tables or even ER diagrams, but to start with a visualisation of the data in a more concrete way, e.g. using diagrams, archive cards, or anything that works for you and -more important- works for the domain expert [for a homework assignment the domain expert is usually just you playing his/her role]. Then follow some guidelines to make modifications to the examples and see if they are still valid - that's how you will discover the functional dependencies. Once you know the functional dependencies, finding the correct relational design is simple).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
[/url]Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply