MYSQL Table Columns HELP..
Moderator: General Moderators
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
MYSQL Table Columns HELP..
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.
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.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MYSQL Table Columns HELP..
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
Choose a storage engine that suits your needs best. I usually use InnoDB
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
@ 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..
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..
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MYSQL Table Columns HELP..
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 ....
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 ....
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
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..
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..
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
year is UG -Sem1-- Student A--2008
UG-Sem2 =-- Student A--2009(if promoted)
UG-Sem2 =-- Student A--2009(if promoted)
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
@ jaoudestudios..
can u help me out boss..
can u help me out boss..
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MYSQL Table Columns HELP..
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?
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?
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
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..
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..
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MYSQL Table Columns HELP..
Ah ok, good
I think you are on the right track.
Can you post your database schema so I can see the full structure?
I think you are on the right track.
Can you post your database schema so I can see the full structure?
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
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..
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..
- Attachments
-
- Excel sheet parsed by php..
this sheet is for UG single branch single semester. - 1.JPG (83.79 KiB) Viewed 1082 times
- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
@ 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 ..
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='';
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MYSQL Table Columns HELP..
I was looking more for a real database schema like this...
So I could check indexing, structure, column types etc...
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 ;- pavanpuligandla
- Forum Contributor
- Posts: 130
- Joined: Thu Feb 07, 2008 8:25 am
- Location: Hyderabad, India
Re: MYSQL Table Columns HELP..
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,
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,
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: MYSQL Table Columns HELP..
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...
Hope that helps
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...
Yes the name xyz is in 3 times. This is not good!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?
Hope that helps