table design

Question

table design

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101123 More actions · Answer 1

hlsc1983 (2/23/2016)
And one more thing. How terrible would it be if I used only one table with a many columns like I said earlier. ? Of course it won't be normalized but how bad will it be.

It really would be awful. For a number of reasons:

1) If you're a professional in the computer field, or intend to be, anyone that looks at a "design" of "everything in one table" will certainly think very little of your design "skills".

2) Normalization is not just a buzz word. It's done for very good reasons, including producing a design that is much more easily changed or enhanced without causing data errors. A denormalized design can cause all sorts of data problems, including "disappearing" data.

3) The application will be much easier to write against a normalized design, particularly for searches.

There are more reasons, I simply don't have time right now to list some others. 😉

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

The main reasons have been listed. Using normalized storage gives you slightly more complex queries with joins, but easier updates, easier and simpler growth if you expand the model, less application development later for the addition or removal of subjects, or marks.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 3

Now that subjects, grades table is settled , please suggest where I should store the actual marks scored by the students .

If I make a junction table between students and subjects, how can I incorporate those different types grades and pass marks and full marks.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 4

k this is what i have been doing so far to record marks of students. i have a marks_master table like this:

marks_id int,

subject_id int,

student_id int,

THET_marks int,

THPA_marks int,

PRPA_marks int,

PRET_marks int,

odd_even varchar,(combo box has only two values-odd or even)

reg_comp varchar( combo box has only two values- regular or compartmental)

year int, (year of examination)

so rows look something like this:

i inert null values for those subjects which do not have the associated grade type. For example, subject_id 113 do not have PrPA and PRET grade types.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

hlsc1983 (2/24/2016)
k this is what i have been doing so far to record marks of students. i have a marks_master table like this:
marks_id int,
subject_id int,
student_id int,
THET_marks int,
THPA_marks int,
PRPA_marks int,
PRET_marks int,
odd_even varchar,(combo box has only two values-odd or even)
reg_comp varchar( combo box has only two values- regular or compartmental)
year int, (year of examination)
so rows look something like this:
i inert null values for those subjects which do not have the associated grade type. For example, subject_id 113 do not have PrPA and PRET grade types.

NO. This is suffering the same normalization problems as your original tables. You need to start thinking about what you are storing. In this case you are storing grades for a student. In this design, what happens if a student takes the same class a second time? You have no place to store it. What happens if you add new subjects? You have to add more columns to the table. I would strongly advise you to spend some time reading about database design and normalization. The stairways series is a great read. You can it by clicking the "Stairways" link over there on the left. Then scroll down to "Database Design".

Here is a table more closely to how I would design this.

create table StudentMarks

(

StudentID int

, SubjectID int

, MarkValue numeric(9,2) --or int if you only want whole numbers

, odd_even bit --varchar is the wrong datatype when it can only hold two values

, reg_comp bit

, SchoolYear int --year is poor column name because it is a reserved word and is pretty ambiguous

, CONSTRAINT [PK_StudentMarks] PRIMARY KEY CLUSTERED

(

StudentID

, SubjectID

, SchoolYear

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

hlsc1983 SSCommitted Points: 1971 More actions · Answer 6

k if a student appear for the second time, then the reg_comp column value will be compartmental.

if i use bit values, then i have to make another table with only two values- odd and even?

regarding new subjects, the user will first have to enter the subject details in a form . later on, if he wants to enter marks for that subject he has to choose the subject from listview. in effect , marks_master table will only accept subject names that are already stored in the database.

i have only shown you a part of the masrk_master table.

lastly, your table does not take into account the various types of grades( theory, practical, end term , progressive).

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

hlsc1983 (2/24/2016)
k if a student appear for the second time, then the reg_comp column value will be compartmental.

Not sure what you mean here. But if this is a value that is related to the student then it would belong with the student table.

if i use bit values, then i have to make another table with only two values- odd and even?

Why do you need a table to hold odd and even. Rename the column to IsEven or IsOdd. It is abundantly clear what it means then

regarding new subjects, the user will first have to enter the subject details in a form . later on, if he wants to enter marks for that subject he has to choose the subject from listview. in effect , marks_master table will only accept subject names that are already stored in the database.

Well yes, why would you store marks for a subject that doesn't exist?

i have only shown you a part of the masrk_master table.
lastly, your table does not take into account the various types of grades( theory, practical, end term , progressive).

Then you just need to add another column for GradeTypeID so you know which type of grade it is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

hlsc1983 SSCommitted Points: 1971 More actions · Answer 8

so like this?

Master_marks table with the following columns:

student_id int,

subject_id int,

marks int,

odd_even bit,

reg_comp bit,

exam_year int, '( year is associated with the year of examination and not student)'

gradeID int,

and a Gradetype table with the following columns:

gradeid int,

gradetype varchar,

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

hlsc1983 (2/24/2016)
so like this?
Master_marks table with the following columns:
student_id int,
subject_id int,
marks int,
odd_even bit,
reg_comp bit,
exam_year int, '( year is associated with the year of examination and not student)'
gradeID int,
and a Gradetype table with the following columns:
gradeid int,
gradetype varchar,

That doesn't look too bad. I would highly suggest using better names for your bit columns. odd_even is very ambiguous. What does a value of 1 mean in there? Is that odd or even? Something like IsOdd would be appropriate I would think. Any time you have a bit column it is a good idea to name it something so it is obvious what 1 and 0 mean. Also, don't forget that in sql a bit has 3 values. 0, 1 and NULL. So you might make those columns NOT NULL depending on requirements.

If you keep marks as an int you can't store values like 84.7%. Maybe this is ok but just a plain int seems a little too restrictive for most grading situations.

My only other suggestion would be to name the GradeType as GradeTypeID instead of GradeID. It is much clearer that it is referring to the type of grade and not the value of the grade.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

hlsc1983 SSCommitted Points: 1971 More actions · Answer 10

Odd means odd semester- I,III, and V semesters

Even means even semester- II, IV and VI semesters.

Regular papers means student appears it for the first time.

Compartmental means the student has flunked it and it appearing it again.

Please elaborate more about the OddEven column. i want the user to select Odd or Even from combo box . so how does bit value fit in here? likewise for Regular and compartmental.

k since u have not mentioned anything about foreign keys, i presume that this table with no foreign keys from other tables like subjects is fine?

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

hlsc1983 (2/24/2016)
Odd means odd semester- I,III, and V semesters
Even means even semester- II, IV and VI semesters.
Regular papers means student appears it for the first time.
Compartmental means the student has flunked it and it appearing it again.

Then I would maybe name the column IsCompartmental, or don't even bother. It would be obvious they are taking it a second time if there is a second grade for a given course. 😉 When the data is normalized you don't have to track some things like this because you can let the data tell you.

Please elaborate more about the OddEven column. i want the user to select Odd or Even from combo box . so how does bit value fit in here? likewise for Regular and compartmental.

Well...if the user picks Odd I would store it as a 1, otherwise a 0.

k since u have not mentioned anything about foreign keys, i presume that this table with no foreign keys from other tables like subjects is fine?

It ABSOLUTELY should have foreign keys. That is how you insure you don't get trash data. Same logic as your other tables, make the foreign keys to things like subjects, students etc... I didn't script it all out for you assuming you could handle that part.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101123 More actions · Answer 12

You need to use the actual semester number to record the grade. Year alone is not enough, nor is "odd/even" (bizarre!).

It really seems like this is some type of assignment or homework and not a real-life application. It's much too simplified to be real. Semester was a critical component of the student data schema when I worked on a university student info system.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

hlsc1983 SSCommitted Points: 1971 More actions · Answer 13

i am not a student . i work in the examination cell and i have been trying to develop an application that will replace the current system.

regarding odd/even, i am including it because in the marksheet hardcopy, it is mentioned whether it was even or odd semester.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

hlsc1983 (2/24/2016)
i am not a student . i work in the examination cell and i have been trying to develop an application that will replace the current system.
regarding odd/even, i am including it because in the marksheet hardcopy, it is mentioned whether it was even or odd semester.

I too was wondering what the EvenOdd semester thing is. It seems strange to me. Is the Fall semester even or odd? It must have relevance where you work but it is quite strange to outsiders.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

hlsc1983 SSCommitted Points: 1971 More actions · Answer 15

k if i insert a gradetypeID column in the table, that means there will be four rows in the table if the subject has all four possible grade types. one row each for theory progressive, theory end term, practical progressive, practical end term each

will it not make the table very big size? and will it affect the performance for eg: take more time while searching all the grades in four years scored by one student?