Need advice on planning tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need advice on planning tables

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-07-31 : 15:21:44

Hello,

I have two tables: Professors and Articles.

I need to classify the lessons given by the professors and their articles using the following information:

- Levels and Grades are as follows:

Elementary School - 1, 2, 3, 4, 5 grades
Middle School - 6, 7, 8 grades
High School - 9, 10, 11, 12 grades
College - I do not distinguish the years.

- Subjects can be for example:

Mathematics, English, History, Physics ...

- Tags (In case of articles)

Tags are usually words related with the each article content.

EXAMPLE:

Professor John can give lessons to:

Mathematics for students of Middle School (7 and 8 grades)
Physics for students of High School (9, 10, 11, 12 grades) and College

Article A content is related to:

Mathematics of Middle School (7 and 8 grades)
And related to Tags = Algebra, Multiplication

I need to be able to filter Articles or Professors. For example:

All Mathematic Articles
All Mathematic Articles for High School
All Mathematic Articles for High School in Grades 7 and 8

All Professors that lecture Mathematics
All Professors that lecture Mathematics in High School
All Professors that lecture Mathematics in High School and Grades 7 and 8

My idea was to have 5 tables:

Professors, Articles, Subjects, Levels and Grades.

However it seems there is a relation between Grades and Levels.
Grade 8 is always on Level High School.
Grade 2 can be in Elementary School or in College (If I define the grades in College)
Note: I can not define any Grade in College and just define a level College.

I was thinking in creating 2 tables as follows:

ArticlesThemes > ThemeId, ArticleId, SubjectId, LevelId, GradeId.
ProfessorsThemes > ThemeId, ProfessorId, SubjectId, LevelId, GradeId.

So every time I create an Article or a Professor I would create on or more themes associated to it.

Then I can leave a SubjectId, LevelId or GradeId empty ... And I can filter for all of them or for only one of them.

However, I don't have that relation between a Grade and a Level.

Could someone, please, tell me what should be the correct approach to plan this?

Thanks,
Miguel

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-31 : 15:33:04

Your Grade 2 example represents 2 different meanings of what Grade 2 represents.

If you want to use the same name, you could use a natural key and make sure that the school or program becomes part of the natural keyu composite

So...

Elementary School, Name of School, Grade 2
College, Name of School, Grade 2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2009-07-31 : 15:57:19

But I think if I forgot the grades of College would be this ok?
Or in that case I should relate the grades to levels?

Or maybe create a relation between Grades and Levels?

One other option would to create a tree structure using HierarchyId Of SQL 2008. Level 1 would be Level, Level 2 would be Grade, Level 3 would be Subject.

But I think the filtering would be very complex and maintaining this tree table would be quite complex.

Another approach would be to forget the Levels and Grades tables and join the two in one table and naming each level like:

1 grade of Elementary School
...
9 grade of High School

But I loose the Level / Grade filters

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources