Page 1 of 2

MYSQL Table Columns HELP..

Posted: Sat Oct 11, 2008 12:04 am
by pavanpuligandla
Hii,,
i'm developing an online portal, which my MYSQL database contains 100 tables..
my doubt is if any table contains 30-40 columns will it become a problem?
like my html front end form has 40 fields for data insertion.
and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems??
as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?
awaiting for ur replies guys..
Many Thanks,
Pavan.P.

Re: MYSQL Table Columns HELP..

Posted: Sat Oct 11, 2008 2:08 am
by jaoudestudios
That will be fine, but make sure you have the most efficient database structure and not putting many rows in a table for lack of sql knowledge.

Choose a storage engine that suits your needs best. I usually use InnoDB

Re: MYSQL Table Columns HELP..

Posted: Sat Oct 11, 2008 6:27 am
by pavanpuligandla
@ above..
thnx for ur reply boss,

my application is an online web portal tht stores marks,grades,attendance semester courses etc..
so, if an exam was conducted the administrator has to insert around 5000 student records of various branches like UG,PG PHD, in which there'll b different subjects so my marks table contains data columns like this:

Name|studentID|Branch|year|examtype|batchID|A1|A2|A3|A4|A5|...|a25|

A1,A2...A25 are subject codes..

i 'm not storing repeated values in any table.
examtype is like term exams, assignments, and 8 more various exams..
so i'vent found better solution than the above explained design..
is my DB proto good or bad?? ??? pls do suggest me..

and i'm using MyISAM..

Re: MYSQL Table Columns HELP..

Posted: Sat Oct 11, 2008 8:39 am
by jaoudestudios
How many people are going to be connecting to the database? If only a few then MyIsam is fine.

Will a1-a25 ever change, increase etc. Will all of them be used for each person? Or will student A use a1-a18 and student B could use a1-a25?

What about YEAR - will a student be entered more than once? i.e. Student A | 2008 ... Student A | 2009 ....

Re: MYSQL Table Columns HELP..

Posted: Sat Oct 11, 2008 12:28 pm
by pavanpuligandla
hii..
yeah very few ppl will connect to d DB.

subject codes are like :
for UG I Semester, II Semester, III semester there'll be a sum of 12 subjects in each semester,
hence my table contains subject codes like A1-English, A2- Algorithms etc..

suppose UG I Semester clas has 100 students, then these 100 student's marks will be stored under these subject codes as rows.

like, Name|ID|Branch|Batch|ExamType|ExamYear|A1|A2|A3|A4....
abc| 1|UG|2008|Assignment1|march-08|20|20|20|20..

hope u got my point..

Re: MYSQL Table Columns HELP..

Posted: Sat Oct 11, 2008 12:30 pm
by pavanpuligandla
year is UG -Sem1-- Student A--2008
UG-Sem2 =-- Student A--2009(if promoted)

Re: MYSQL Table Columns HELP..

Posted: Sun Oct 12, 2008 3:49 am
by pavanpuligandla
@ jaoudestudios..

can u help me out boss..

Re: MYSQL Table Columns HELP..

Posted: Sun Oct 12, 2008 6:17 am
by jaoudestudios
So a student could go in multiple times one for each year or sem.

In this case a better design would be to put the student in a separate table and have a foreign key in the results table. This would give you more flexibility to expand on the database in the future but on the other hand it would make your queries a little more complicated as you would have to join multiple tables.

What is your SQL like?

Re: MYSQL Table Columns HELP..

Posted: Sun Oct 12, 2008 6:48 am
by pavanpuligandla
i'm using MYSQL.

yeah, i'm using seperate tables for students and their courses registered..
seperate tables for subject codes and marks secured.
separate tables for all years and for attendance records etc..

Re: MYSQL Table Columns HELP..

Posted: Sun Oct 12, 2008 7:30 am
by jaoudestudios
Ah ok, good :)

I think you are on the right track.

Can you post your database schema so I can see the full structure?

Re: MYSQL Table Columns HELP..

Posted: Mon Oct 13, 2008 12:32 am
by pavanpuligandla
yeah sure,

as i'm using excel as front end form.. each table contains
Name, ID, Branch, ExamType, Subject codes of the respective courses ..

as UG is a 4 year(8 semesters) course, eac semester has 8 different subjects.. so UG course of electrical branch will b having 64 different subjects, and UG has various branches like computers, arts, electronics, mechatronics, civil etc..

i'm herewid ataching my excel sheet(parsed by php) which is inserted into mysql table via PHP..

Re: MYSQL Table Columns HELP..

Posted: Mon Oct 13, 2008 1:15 am
by pavanpuligandla
@ above IMAGE..

A1BS01 , S02.. are subject codes..they will not remain same..so they are UNIQUE ID's
exam type refers to the name of exam conducted either internal or external or lab exam or an assignmnet.Batc refers to the students admitted in 2008, batch or 2009 etc..
so tht table gives a clear picture of a student of any batch of any exam type of any year conducted for.

i think this is a better solution rather than creating separate tables for subject codes..
if i were using a HTML form, i would be using another DB prototype like shown below ..

Code: Select all

Student Master           
 Joining Year CollegeCode Branch SerialNo.  Branch Master  
HallTicketNo:    Code 05 Primary key  
Name    Short Description CSC  
JoinedSemister    Description Computer Science  
Password    
Role Foreign Key refering to Role Master  Subject Master  
Father Name    Code  Primary key  
Mother Name    Description  
Guardian Name    Type  
Admission No. Primary Key   Credits  
Admission Date    Year  
Date of Birth    Semister  
Religion    Branch  
Category OC/BC/SC/ST  MinPassing  
Blood Group    MaxPassing  
Admision Type    
eMail ID    Exam Master  
PAddress1    Code Mid1 Primary key  
PAddress2    Description 1st Internal   
PStreet    
PLandmark    Status Master  
PTown    Code  01 Primary key  
PState    Description Pass  
CAddress1    
CAddress2    Role Master  
CStreet    Code 01  
CLandmark    Description User  
CTown    
CState    
Fphone    
Mphone    Marks Master  
Gphone    *HallTicketNo. Foreign Key refering to Student Master 
Landline    *Semister  
Pphone    ConductedDate  
Extra Circular Activities   *ECode Foreign Key refering to Exam Master  
   *SCode Foreign Key refering to Subject Master 
Previous Acadimia(Student Child)  MarksObtained  
Admission No Foreign Key refering to Student Master  *StatCode Foreign Key refering to Status Master  
Course    combination all stars is Primary key for Marks Master 
Studied At    
District    
PerRank    
   
 View existing data for previous exams attended against Hallticketno  
 select b.Hallticketno,b.Semister,c.Description,d.description,b.marksobtained,e.description from Marks_master b,Exam_master c,Subject_master d,Status_master e where c.code=b.ecode and d.code=b.scode and e.code=b.statcode where hallticketno='' and semister='';  
 

Re: MYSQL Table Columns HELP..

Posted: Tue Oct 14, 2008 1:51 am
by jaoudestudios
I was looking more for a real database schema like this...

Code: Select all

 
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `firstname` varchar(64) NOT NULL,
  `lastname` varchar(64) NOT NULL,
  `email` varchar(128) NOT NULL,
  `password` varchar(128) NOT NULL,
  `telephone` varchar(32) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('pending','inactive','active') NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
So I could check indexing, structure, column types etc...

Re: MYSQL Table Columns HELP..

Posted: Wed Oct 15, 2008 8:13 am
by pavanpuligandla
hii..sorry for d dealy response,

what is redundancy?
i want to cut down the no:of coumns in mysql table..but i'm using excel as front end form, which will contain columns as shown in d above image..

i think the image shown above not contains redundant data.

if i redesign mysql table show in above image as::

Name |studentID|BranchID|year|examtype|batchID|Exam|marks|Result|
+----+---------+------++----+-----+----+--------+-------+----+------+
|John| 124 | 102 | |2008| UG | 08 | A1 | 86 |Pass
|Jane| 428 | 102 | |2008| UG | 08 | A2 | 91 |Pass
|xyz | 114 | 102 | |2008| UG | 08 | A3 | 68 |Fail
|xyz | 114 | 102 | |2008| UG | 08 | A1 | 88 |Pass
|xyz | 114 | 102 | |2008| UG | 08 | A2 | 78 |Pass
|chris | 115 | 102 | |2008| UG | 08 | A1 | 28 |Fail
+----+---------+------++----+-----+----+--------+-------+----+------+

is this redundant data?
instead of maintaining subject codes as columns, if i cut down all those columns to one column
with "EXAM":
will this b an optimal solution to my table???

please give me a solution...u can check my original table with 21 columns in the above image, and which i posted here is a revised table..
many thanks,

Re: MYSQL Table Columns HELP..

Posted: Wed Oct 15, 2008 2:00 pm
by jaoudestudios
Redundancy - we do not want duplicated data. There is no advantage of it here.

I would keep the many columns if it is easier for you or do what you just suggested, BUT definitely split up the users. So put the users in a different table and have their unique id in the results table. That way you can hang off other information of the user, i.e. address, number, email etc...
if i redesign mysql table show in above image as::

Name |studentID|BranchID|year|examtype|batchID|Exam|marks|Result|
+----+---------+------++----+-----+----+--------+-------+----+------+
|John| 124 | 102 | |2008| UG | 08 | A1 | 86 |Pass
|Jane| 428 | 102 | |2008| UG | 08 | A2 | 91 |Pass
|xyz | 114 | 102 | |2008| UG | 08 | A3 | 68 |Fail
|xyz | 114 | 102 | |2008| UG | 08 | A1 | 88 |Pass
|xyz | 114 | 102 | |2008| UG | 08 | A2 | 78 |Pass
|chris | 115 | 102 | |2008| UG | 08 | A1 | 28 |Fail
+----+---------+------++----+-----+----+--------+-------+----+------+

is this redundant data?
Yes the name xyz is in 3 times. This is not good!

Hope that helps