[menukadevinda]

Hi all,

I develop a student information system with vb.net where marks of previous years and preference of subjects of students are recorded. finally according to their preference and available resources at the department, students are granted two subjects for each one for higher studies.

In the system we have to get information and out put the student detatils and their subject.


problem is there are all 5 subjects. Each student has to select 1 major subject out of 5 and rest of 4 subject for major 2 subject according to preference. again students have to arrange other 4 major subjects since studnt may not be selected for the fist major1 subject.

there fore there are 25 subjects 5 major and 20 major2 subjects. so do I have to create db table with 25 coloumns. I feel unconforatable with that. What if I put only major subject with id and use another table foe major2 subject??

plz help me...
thanks in advance,
menuka

[Jstall]

Hi,

It seems that you would want to make some associative tables to connect student and subject. Something like this:

[menukadevinda]

thanks dear, but I want to know why If I do all this in one table, then no repetition and one row

plz reply

[baavgai]

First, having a table named major and an identical one named major_2 is probably a bad idea.

I'd do something like:

Student
   StudentId (PK)
   Name
   ...

Subject
   SubjectId(PK)
   Name
   ...


StudentSubject
   StudentId (PK,FK)
   SubjectId(PK,FK)
   IsMajor

Or, perhaps, if a student may have one and only one major:

Student
   StudentId (PK)
   Name
   MajorId (FK)
   ...

StudentMinor
   StudentId (PK,FK)
   SubjectId(PK,FK)

If not all Subjects can be majors, then:

Subject
   SubjectId(PK)
   Name
   IsMajor
   ...

It really has a lot to do with all the elements involved, but that's a start.

menukadevinda, on 12 October 2011 - 09:08 AM, said:

thanks dear, but I want to know why If I do all this in one table, then no repetition and one row

It's a bad design for two reasons. First, what if you add another major that needs to be considered? You have to change table structure, which you want to avoid.

In a more practicle example, if you have to find all the students in a given subject? In a reasonably normalized database, you just do a join. In your database, you do something like:

where a.id1=b.subject_id or a.id2=b.subject_id or a.id2=b.subject_id...

It's sloppy, slow, and error prone.

[DarenR]

I would create a table per major with the identifier being the major and one just for students. I know its a lot more tables but it will give you a cleaner look and easier to find things in the long run.

[Atli]

DarenR, on 12 October 2011 - 01:18 PM, said:

I would create a table per major with the identifier being the major and one just for students. I know its a lot more tables but it will give you a cleaner look and easier to find things in the long run.

That's no better than creating a column for each major, for the exact same reasons baavgai just wrote. What if you have to add or remove majors? You'd have to add or remove an actual table, which is something you should not do in a relational database. Data goes into tables, it doesn't become tables.

Also, I don't get how this would make things easier. As I see it, this would only complicate queries. It may even make it impossible to create some queries.

[baavgai]

DarenR, on 12 October 2011 - 09:18 AM, said:

I would create a table per major

Why?!? Does the data in each major differ that much?

Tables are just structure in which you organized related items. Just having a label for something like "major" implies that "majors" would all be similar. If even if they differ in things like categories, groupings, whatever properties you can name, you'd model that in DATA not STRUCTURE.

Note that what you see as a simpler design might be a nightmare in the long run. You must look at not just inserting the data, but at getting it back out. Often, the easiest design for inserting is the worst possible case for retrieval.

What questions will you ask of your data? How many students are in a given major? How may are in a secondary subject? What students are taking a given class? And on and on. Not all these questions will be easy to express, but a poorly designed database will make them much, much, harder.

[menukadevinda]

in the above programm anybody can have one major1 subject and one major2 subject. againsta any major1 subject student have 4 major major2 subjects. He has to select 1 from out of 4 major2 subjects, like wise now he has 5 choices for major1.

this is for more understanding..

[menukadevinda]

this is how I design table for subjects here

studentMajor1 table
stid majoId major1sub
1212 1 maths
1212 2 mangment
1212 3 stat
1212 4 cms
1200 1 maths

studentMajor2 table

stuid major1Id major2
1212 1 stat
1212 1 cms
1212 1 mang
1212 2 stat
1212 2 mat
1212 2 cms

studnet

studNOo Name in Full Name with Initials Address Contact No. Date of Birth Gender
092008 Amila Bandula Perera A.B.Perera 54,Wattala 011-564515 14.5.1989 M
092109 Amali Gunasekera A.Gunasekera 65,Church road,Kandy 081-156545 1.5.1988 F
092111 Kasun Supun Pathirana K.S.Pathirana "Ramya",Nugawela,Colombo 011-654654 1.12.1988 M
092133 Sandya Umayangani Samarasinghe S.U.Samarasinghe 4,Mathale 081-545684 18.6.1988 F


if there

[Curtis Rutland]

Bad idea. Baavgai has explained why two major tables is a bad idea. One subject table, and the row can have two boolean identifiers. Call them IsMajor and IsMinor, or IsMajor1 and IsMajor2, if you will. This way, any subject that can be both majors or minors can be marked as such, otherwise, only mark one of the identifiers.

Then, your student table can have a MajorId and a MinorId, which can point to a row in the subject table. Two tables, properly normalized. No unnecessary data repetition.

[baavgai]

How about:

major table
major_id  major_name
1         maths
2         mangment
3         stat
4         cms

student_major table
student_id  major_id is_primary
092008      1        1
092109      2        1
092111      3        1
092133      4        1
092008      2        0
092109      1        0
092111      2        0
092133      1        0
092133      2        0

student_major
student_id (PK,FK)
major_id   (PK,FK)
major_type (Business rule, only one is_primary per student_id)