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